Changing the Schema of All Tables

Sometimes we require to change the schema of all the tables in a database.

For example, you have a database whose tables are in dpu schema and now you want to change the schema to dbo,  use the folowing code:


DECLARE @sql Varchar(8000), @table Varchar(1000), @SchemaToChange Varchar(1000), @NewSchema Varchar(1000)

SET @SchemaToChange = 'dpu'
SET @NewSchema = 'dbo'

WHILE exists(SELECT * FROM sys.tables WHERE schema_name(schema_id) = @SchemaToChange)
BEGIN
SELECT @table = name from sys.tables WHERE object_id in
       (SELECT min(object_id) FROM sys.tables WHERE schema_name(schema_id) = @SchemaToChange)

SET @sql = 'alter schema ' + @NewSchema + ' transfer ' + @SchemaToChange + '.' + @table

EXEC(@sql)

END