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

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".

⇒ "sys.objects" - Listing All Objects in a Given Schema in SQL Server

⇐ Creating a New Table in a Given Schema in SQL Server

⇑ Creating and Managing Schemas in SQL Server

⇑⇑ SQL Server Database Tutorials