Tuesday, February 2, 2010

How to change the schema for a table in SQL Server

Here’s the situation, you have a table that is in some schema and you want to move it to another schema in SQL Server 2005 (probably for 2008 also). In my example, I created the table using MS SQL Sever Management Studio to create a table. It puts it in the dbo schema. I want it to be in MySchema schema. I also have some code generation tools that require that it be in dbo schema. So, I have the need to change a table from one schema to another.

Thankfully there is an easy to use T-SQL statement for addressing this issue.

Move table from dbo schema to MySchema

ALTER SCHEMA MySchema TRANSFER dbo.MyTable

Move table from MySchema to dbo schema

ALTER SCHEMA dbo TRANSFER MySchema.MyTable

3 comments:

Neil said...

Was looking for some information on this. Thanks for the help.

Anonymous said...

Thanks for Sharing!!!!

Anonymous said...

Rats, got that wrong on my SQL certification test