Change schema of all tables in database

To change schema of any table/stored procedure you can use this command: ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName But when you want to change schema of all table/sp of a database then its not a good idea to write such lines for all objects. better idea is to generate such script and then run it.. to generate such script for all tables, stored procedures and views, you can use below script.

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER ' + SysSchemas.Name + '.' + DbObjects.Name + ';'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

now just copy the output of this script and run...

Compiled by: Rajesh Rolen