MSSQL: Rename a database schema.

Posted August 13th, 2011


Generates a set of queries to run which will change the schema for all items within a specified schema and change them to another.

DECLARE @sourceschema VARCHAR(100)
DECLARE @destinationschema VARCHAR(100)
SET @sourceschema = 'source_schema'
SET @destinationschema = 'destination_schema'
SELECT 'ALTER SCHEMA ' + @destinationschema + ' TRANSFER [' + b.name + '].[' + a.name + ']' FROM sys.objects a JOIN sys.schemas b
ON a.schema_id=b.schema_id WHERE a.type IN ('U','P','FN','V') AND b.name=@sourceschema