Friday, July 29, 2016

How to change schema of all tables, views and stored procedures in MSSQL

Yes, it is possible.
To change the schema of a database object you need to run the following SQL script:
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName
Where ObjectName can be the name of a table, a view or a stored procedure. The problem seems to be getting the list of all database objects with a given shcema name. Thankfully, there is a system table named sys.Objects that stores all database objects. The following query will generate all needed SQL scripts to complete this task:

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'))
Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

Now you can run all these generated queries to complete the transfer operation.

Reference : http://stackoverflow.com/questions/17571233/how-to-change-schema-of-all-tables-views-and-stored-procedures-in-mssql