Please enable Javascript for better experience...
How to Change Schema Name Of a Table In SQL SERVER
By Big | Aug 4, 2015 | In Tips | Update: Aug 4, 2015 | Total Views [ 960 ]
Taged In
(0 Like)
Rate

You can use TRANSFER keyword to change the SCHEMA name of your table in Sql Server.

The Problem

If you have created your table in wrong schema or you want to move your existing table to another schema in SQL, you can perform this ALTER using TRANSFER command.

The Solution

Let's create a table in schema DBO and try to move it in another schema.

Create table

Create a table EMPLOYEE_DETAIL in schema [DBO]

--Create table EMPLOYEE_DETAIL in schema DBO. This is default schema
CREATE TABLE [DBO].[EMPLOYEE_DETAIL](
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(50),
ADDRESS VARCHAR(250),
ISACTIVE BIT NOT NULL DEFAULT(1),
[DATE] DATETIME NOT NULL DEFAULT(GETDATE())
)

Create Schema

Create a new EMPLOYEE schema.

You can create new schema using CREATE SCHEMA SCHEMA_NAME command.

--Create Schema EMPLOYEE
CREATE SCHEMA EMPLOYEE

Change table schema

To change table schema use alter command with transfer keyword.

Use ALTER SCHEMA [NEW_SCHEMA] TRANSFER [OLD_SCHEMA].[TABLE_NAME] to change schema name of table.

--Move EMPLOYEE_DETAIL table from schema [DBO] to [EMPLOYEE]
ALTER SCHEMA EMPLOYEE
TRANSFER [DBO].[EMPLOYEE_DETAIL]

Table is successfully moved from schema [DBO] to new schema [EMPLOYEE]. Hope this help you.

Share this

About the Author

Big
Rahul Jha (Big)
Founder, Developer dotnet-concept.com
Has working experience in different phases of Software Development Life Cycle (SDLC) in CMS, Gaming, Health Care and Financial Services domain using Agile pattern. Working experience in Design patterns, ASP.NET, MVC, Windows application, WCF, ADO.NET, SQL Server and Test Driven Development (TDD) environment with JQuery, JavaScript, N-Unit, Entity Frameworks, LINQ, Code Refactoring and Business Objects Models.

 
Please SignUp/Login to comment...

Or comment as anonymous...
* Name
* Email ID
Comment