How to move an object from one schema to another schema

If you have created a view|Procedure|Table in the wrong schema in SQL Server, you need to run this script.


If you have created more than one objects in the wrong schema, you can join the system table named sys.Objects, and sys.Schemas, like this.

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'))

Dette indlæg blev udgivet i SQL Server - scripts. Bogmærk permalinket.