Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.

For example, if I create a table using below script it will be created under my default schema which is dbo:

USE [SqlAndMe]

GO

CREATE TABLE Employee

(

       ID     INT,

       Name VARCHAR(20)

)

GO

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      dbo

(1 row(s) affected)

As you can see from the output the table is currently in dbo schema.

Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.

USE [SqlAndMe]

GO

CREATE SCHEMA HumanResources

GO

ALTER SCHEMA HumanResources

TRANSFER dbo.Employee

GO

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      HumanResources

(1 row(s) affected)

As you can see from the output the Employee table is now moved to HumanResources schema.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data