Sunday, February 19, 2017

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.

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.

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;


No comments:

Post a Comment