Move the schema object from one schema to another in SQL Server

Move object in between schema in SQL Server

Syntax:

ALTER SCHEMA new_schema TRANSFER old_schema.object_name; 

Example: Move the scott schema objects into dbo:

ALTER SCHEMA dbo TRANSFER scott.employees;

Transfer all objects present or created in the different schema:

SELECT 'ALTER SCHEMA dbo TRANSFER scott.'+name+';' where sys.objects where schema_name(schema_id) = 'scott';

Example:
ALTER SCHEMA dbo TRANSFER scott.employee;
ALTER SCHEMA dba TRANSFER scott.department;
Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.