How do I move a table into a schema in T-SQL

I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.

Short answer:

ALTER SCHEMA new_schema TRANSFER old_schema.table_name

I can confirm that the data in the table remains intact, which is probably quite important :)

Long answer as per [MSDN docs][1],

ALTER SCHEMA schema_name
TRANSFER [ Object | Type | XML Schema Collection ] securable_name [;]

If it's a table (or anything besides a Type or XML Schema collection), you can leave out the word Object since that's the default.


[1]:

[To see links please register here]

ALTER SCHEMA TargetSchema
TRANSFER SourceSchema.TableName;

If you want to move *all* tables into a new schema, you can use the undocumented (and to be deprecated at some point, but unlikely!) `sp_MSforeachtable` stored procedure:

exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"


Ref.: [`ALTER SCHEMA`][2]

[SQL 2008: How do I change db schema to dbo][1]

[1]:

[To see links please register here]

[2]:

[To see links please register here]