Changing Schema of SQL Server objects

I had a requirement today to change the schema of set of tables to new schema but I did not find a direct method to change the schema of all objects using a single statement. The ALTER SCHEMA supports transferring one object from one schema to another but it cannot be executed against multiple tables.

view plainprint?

1. USE AdventureWorks2014;  
2. GO  
3.   
4. -- transferring Person table from Person Schema to Sales  
5. -- Once executed, tables becomes Sales.Person  
6. ALTER SCHEMA Sales TRANSFER Person.Person;  

USE AdventureWorks2014;
GO

-- transferring Person table from Person Schema to Sales
-- Once executed, tables becomes Sales.Person
ALTER SCHEMA Sales TRANSFER Person.Person;

Therefore I wrote a simple code for transferring multiple tables (of course code can be changed for addressing any object type) and thought to share it because you may look for something similar if you have the same need.

view plainprint?

1. USE AdventureWorks2014;  
2. GO  
3.   
4.   
5. DECLARE @TableNames TABLE (Id int identity(1,1) PRIMARY KEY, Name varchar(500));  
6. DECLARE @Messages TABLE (Id int identity(1,1) PRIMARY KEY, Message varchar(1000));  
7.   
8. DECLARE @TableName varchar(500);  
9. DECLARE @TableId int = 1  
10. DECLARE @NewSchema varchar(20) = 'Sales';  
11. DECLARE @OldSchema varchar(20) = 'Production';  
12. DECLARE @Statement varchar(500)  
13.   
14. -- table all table names needed  
15. INSERT INTO @TableNames  
16.  (Name)  
17. SELECT s.name + '.' + t.name TableName  
18. FROM sys.tables t  
19.  INNER JOIN sys.schemas s  
20.   ON t.schema_id = s.schema_id  
21. WHERE s.name = @OldSchema  
22.  AND t.type = 'U';  
23.   
24. -- making the ALTER SCHEMA statement for all tables  
25. -- and execute them using EXEC  
26. WHILE EXISTS (SELECT * FROM @TableNames WHERE Id = @TableId)  
27. BEGIN  
28.  SELECT @TableName = Name FROM @TableNames WHERE Id = @TableId;  
29.  SET @Statement = 'ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName;  
30.  BEGIN TRY  
31.   
32.   EXEC (@Statement);  
33.   -- record success message  
34.   INSERT INTO @Messages (Message) VALUES ('Successfully transfered ' + @TableName + ' to ' + @NewSchema);  
35.  END TRY  
36.  BEGIN CATCH  
37.     
38.   -- record the error  
39.   INSERT INTO @Messages (Message) VALUES ('Transfer unsuccessful: ' + @TableName + ' [' + ERROR_MESSAGE());  
40.  END CATCH  
41.   
42.  SET @TableId += 1;  
43. END  
44.   
45. -- checking the output  
46. SELECT * FROM @Messages;  

USE AdventureWorks2014;
GO


DECLARE @TableNames TABLE (Id int identity(1,1) PRIMARY KEY, Name varchar(500));
DECLARE @Messages TABLE (Id int identity(1,1) PRIMARY KEY, Message varchar(1000));

DECLARE @TableName varchar(500);
DECLARE @TableId int = 1
DECLARE @NewSchema varchar(20) = 'Sales';
DECLARE @OldSchema varchar(20) = 'Production';
DECLARE @Statement varchar(500)

-- table all table names needed
INSERT INTO @TableNames
 (Name)
SELECT s.name + '.' + t.name TableName
FROM sys.tables t
 INNER JOIN sys.schemas s
  ON t.schema_id = s.schema_id
WHERE s.name = @OldSchema
 AND t.type = 'U';

-- making the ALTER SCHEMA statement for all tables
-- and execute them using EXEC
WHILE EXISTS (SELECT * FROM @TableNames WHERE Id = @TableId)
BEGIN
 SELECT @TableName = Name FROM @TableNames WHERE Id = @TableId;
 SET @Statement = 'ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName;
 BEGIN TRY

  EXEC (@Statement);
  -- record success message
  INSERT INTO @Messages (Message) VALUES ('Successfully transfered ' + @TableName + ' to ' + @NewSchema);
 END TRY
 BEGIN CATCH
  
  -- record the error
  INSERT INTO @Messages (Message) VALUES ('Transfer unsuccessful: ' + @TableName + ' [' + ERROR_MESSAGE());
 END CATCH

 SET @TableId += 1;
END

-- checking the output
SELECT * FROM @Messages;