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 [' + + '].[' + + ']' FROM sys.objects a JOIN sys.schemas b
ON a.schema_id=b.schema_id WHERE a.type IN ('U','P','FN','V') AND