Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Transferring Tables from One Schema to Another

By: FYIcenter.com

(Continued from previous topic...)

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

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

(Continued on next topic...)

1. What Is a Schema in SQL Server 2005?
2. How To Create a New Schema in a Database?
3. How To List All Schemas in a Database?
4. How To Create a New Table in a Given Schema?
5. How To Transfer an Existing Table from One Schema to Another Schema?
6. How To List All Objects in a Given Schema?
7. What Is the Default Schema of Your Login Session?
8. Who Is the Owner of a Schema?
9. How To Change the Ownership of a Schema?
10. What Happens If You Are Trying to Access a Schema Not Owned by You?
11. How To Drop an Existing Schema?