Transferring Tables from One Schema to Another in SQL Server

Q

How To Transfer an Existing Table from One Schema to Another Schema in SQL Server?

✍: FYIcenter.com

A

If you want to move an existing table from one schema to another schema, you can use the "ALTER SCHEMA ... TRANSFER ..." statement as shown in the tutorial exercise below:

-- Login with "sa"

USE FyiCenterData;
GO

-- Confirming that "fyi_links" is in "dbo"
SELECT t.name AS table_name, t.type_desc, 
   s.name AS schema_name
   FROM sys.tables t, sys.schemas s
   WHERE t.schema_id = s.schema_id
   AND t.name = 'fyi_links';
GO
table_name  type_desc   schema_name
----------- ----------- ------------
fyi_links   USER_TABLE  dbo

-- Moving a table to a new schema
ALTER SCHEMA fyi TRANSFER fyi_links;
GO

-- Confirming that "fyi_links" is moved to "fyi"
SELECT t.name AS table_name, t.type_desc, 
   s.name AS schema_name
   FROM sys.tables t, sys.schemas s
   WHERE t.schema_id = s.schema_id
   AND t.name = 'fyi_links';
GO
table_name  type_desc   schema_name
----------- ----------- ------------
fyi_links   USER_TABLE  fyi

The last query confirms that table "fyi_links" is now in schema "fyi".

 

Creating and Managing Schemas in SQL Server

⇒⇒SQL Server Database Tutorials

2016-10-22, 584👍, 0💬