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

Question

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

Rosenkranz

If you need to move multiple tables to a new schema see Renaming multiple tables – Falcone

Answer 1

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

SQL 2008: How do I change db schema to dbo

Stichous

Answer 2

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,

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.

Lemuellemuela

Why the downvote for my correct answer? Please check mine against the accepted answer, which is the same, and has 257 upvotes already (including mine). – Lemuellemuela

Have an upvote for confirming the data remains intact, which just saved my blood pressure... :) – Hessian

@DaveBoltnman: you've entered an answer 6 years after the accepted answer and by your own comment above "which is the same" . – Stichous

@MitchWheat - thanks for that observation :) I did add something which is probably important to many, and which the original and accepted answer did not say, namely "I can confirm that the data in the table remains intact" – Lemuellemuela

why wouldn't data in a table stay intact just because the table is moved into a different schema container? – Stichous

Well, I can run some DDL on my server, and create a table identical to that on your server. But mine doesn't have your data in it. Now you're running some DDL on your server to move a table to a different schema. Does it still have your data in it? I'm confirming that it does, "which is probably quite important :)" – Lemuellemuela

Oh, I +1'd the accepted answer 6 years after. Guess that's also invalid :| – Lemuellemuela

@MitchWheat, FYI please see meta.stackexchange.com/questions/23996/… - By far the majority agree that answering old questions is a good idea. – Lemuellemuela

nice name change @DaveBoltman. Answering old questions is great, when they add something to existing answers. – Stichous