November 20, 2014 05:35 by
Peter
From SQL Server 2005, all tables are grouped into schemas. Even though making a table in case the schema name isn't specified it's developed inside the default schema from the user making it. You are able to use ALTER SCHEMA command to move tables in among schemas. For instance, in case I develop a table using below script it is going to be developed below my default schema that is dbo:
USE [hostsql]
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 are able to notice coming from the output the table is currently in dbo schema. Currently to move this table to another schema utilizing ALTER SCHEMA command, first we have to create the schema in case it doesn't exist by now. When that many of us can move table to new schema.
USE [SqlAndMe]
GO CREATE SCHEMA Staff
GO
ALTER SCHEMA Staff
TRANSFER dbo.Employee
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE name = 'Employee'
GO
Result Set:
name schema
Employee Staff
(1 row(s) affected)
As you can see from the result, the table of Employee is now moved to Staff schema.