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.

ALTER SCHEMA [NewSchemaName] TRANSFER [OLDSCHEMA].[ObjectName]

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.