How to move a table from one schema to different schema..

Problem: How to move a table from one schema to different schema.

Solution:

ALTER SCHEMA, starting with Microsoft SQL Server 2008 or higher

This DDL command helps us to transfers a securable between schemas. Believe me this is as simple as any other alter statements.

Example:

I am going to explain how we are going to move an object from a default dbo schema to a new schema.

Steps:

·         create a new table called dbo.test with two columns

·         create new schema called test

·         moving the dbo.test to test schema and access the test

below is the script:

-- Creating table with dbo schema

CREATE TABLE dbo.Test  

    (Test_id int NOT NULL, 

    Test_Name char(5) NOT NULL) 

GO

-- verify the table has been created

select Schema_name(schema_ID) as SchemaName, name as Table_Name from sys.tables where name='test'

GO

  -- Creating a new schema call test

CREATE SCHEMA Test; 

GO 

 

  --moving the test table to new schema created.

ALTER SCHEMA Test TRANSFER OBJECT::dbo.Test; 

GO 

-- verify the table after moving to new schema

select Schema_name(schema_ID) as SchemaName, name as Table_Name from sys.tables where name='test'