303

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

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
Lukasz
  • 8,710
  • 12
  • 44
  • 72
  • 1
    If you need to move multiple tables to a new schema see [Renaming multiple tables](http://stackoverflow.com/questions/10942901/renaming-multiple-tables) – Tony Apr 22 '13 at 15:56

2 Answers2

518
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

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
22

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.

Reversed Engineer
  • 1,095
  • 13
  • 26
  • 1
    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). – Reversed Engineer Jan 20 '16 at 11:55
  • 2
    Have an upvote for confirming the data remains intact, which just saved my blood pressure... :) –  Feb 07 '16 at 23:05
  • 10
    @DaveBoltnman: you've entered an answer 6 years after the accepted answer and by your own comment above "which is the same" . – Mitch Wheat Oct 13 '16 at 07:47
  • 1
    @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" – Reversed Engineer Oct 14 '16 at 14:32
  • 4
    why wouldn't data in a table stay intact just because the table is moved into a different schema container? – Mitch Wheat Oct 15 '16 at 02:02
  • 1
    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 :)" – Reversed Engineer Nov 30 '16 at 13:50
  • Oh, I +1'd the accepted answer 6 years after. Guess that's also invalid :| – Reversed Engineer Nov 30 '16 at 14:02
  • 1
    @MitchWheat, FYI please see https://meta.stackexchange.com/questions/23996/does-it-make-sense-to-answer-old-questions - By far the majority agree that answering old questions is a good idea. – Reversed Engineer Jul 18 '19 at 08:32
  • 4
    nice name change @DaveBoltman. Answering old questions is great, when they add something to existing answers. – Mitch Wheat Jul 18 '19 at 09:21