Oct 17, 2015

How to change schema of table in SQL Server

In SQL Server 2005 and above versions  all tables grouped into schemas. When we creating a table without specify a schema SQL Server creates a default schema "dbo".  We can alter the schema of table.
For example I am creating a table with default schema see bellow script.

CREATE TABLE Codefari
(
            id INT IDENTITY(1,1),
            title VARCHAR(400)
)
SELECT name,  SCHEMA_NAME(schema_id) as [Schema]
FROM   sys.tables
WHERE  name = 'Codefari'

Result set

name            Schema
------------------------------------------------
Codefari       dbo

(1 row(s) affected)

In result set we can see schema is dbo, if we want change schema of table then first we have to create a schema if does not exist. See below script.

CREATE SCHEMA Blog
GO
ALTER SCHEMA Blog
TRANSFER dbo.Codefari
GO

SELECT name,  SCHEMA_NAME(schema_id) as [Schema]
FROM   sys.tables
WHERE  name = 'Codefari'

Result Set

name           Schema
--------------------------------------------------
Codefari       Blog

(1 row(s) affected)


Now you we can see schema of Codefari table has been change as Blog.