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 specifying a schema SQL Server creates a default schema "dbo".  We can alter the schema of the 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 the result set, we can see schema is dbo, if we want change schema of the table then first we have to create a schema if does not exist. See the 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'

ResultSet

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

(1 row(s) affected)



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

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...