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

Sql ServerSql Server-2008TsqlSchemaSql Server-2016

Sql Server Problem Overview

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

Sql Server Solutions

Solution 1 - Sql Server

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

Solution 2 - Sql Server

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.

Categories
Recommended Sql Server Solutions
Recommended Sql Server-2008 Solutions
Recommended Tsql Solutions
Recommended Schema Solutions
Recommended Sql Server-2016 Solutions

Previous Solution:

What is a unix command for deleting the first N characters of a line?

BashUnixCommandTruncate

Next Solution:

Where do you store your salt strings?

SecurityAuthenticationHashCryptographySalt

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionLukaszView Question on Stackoverflow
Solution 1 - Sql ServerMitch WheatView Answer on Stackoverflow
Solution 2 - Sql ServerReversed EngineerView Answer on Stackoverflow