Moving database objects between schemas | SansSQL

Monday, February 24, 2014

Moving database objects between schemas

Sometimes database objects gets created under different schema names.
This can be intentional or accidental. In either of the cases, if you decide to move an database object between schema then you can make use of the below script.

To move a database object to between schema
ALTER SCHEMA [Target Schema Name] TRANSFER [SchemaName].[ObjectName]

To move a database object to dbo schema
ALTER SCHEMA [dbo] TRANSFER [SchemaName].[ObjectName]

To move multiple database objects between schema, execute the result set generated by the below query
SELECT 'ALTER SCHEMA [Target Schema Name] TRANSFER ['+SCHEMA_NAME([schema_id])+'].['+[name]+']' 
FROM sys.objects WHERE SCHEMA_NAME([schema_id]) NOT IN ('dbo', 'sys')

No comments:

Post a Comment