SQL Server/T-SQL Tutorial/User Role/schema

Create a new schema

9> CREATE SCHEMA Sales
10> GO
1>
2> CREATE SCHEMA Purchases
3> GO
1>
2>
3> --Create a table in the schema
4> CREATE TABLE Sales.SalesData
5> (
6>     SaleNumber INT,
7>     SaleDate DATETIME
8> )
9> GO
1>
2>
3> --Move the SalesData table into the new schema
4> ALTER SCHEMA Purchases
5> TRANSFER Sales.SalesData
6> GO
1>
2> --Reference the table by its new schema name
3> SELECT *
4> FROM Purchases.SalesData
5> GO
SaleNumber  SaleDate
----------- -----------------------
(0 rows affected)
1>
2> drop table Purchases.SalesData
3> drop SCHEMA Purchases;
4> drop SCHEMA Sales;
5> GO

Move tables between schemas

9> CREATE SCHEMA Sales
10> GO
1>
2> CREATE SCHEMA Purchases
3> GO
1>
2>
3> --Create a table in the schema
4> CREATE TABLE Sales.SalesData
5> (
6>     SaleNumber INT,
7>     SaleDate DATETIME
8> )
9> GO
1>
2>
3> --Move the SalesData table into the new schema
4> ALTER SCHEMA Purchases
5> TRANSFER Sales.SalesData
6> GO
1>
2> --Reference the table by its new schema name
3> SELECT *
4> FROM Purchases.SalesData
5> GO
SaleNumber  SaleDate
----------- -----------------------
(0 rows affected)
1>
2> drop table Purchases.SalesData
3> drop SCHEMA Purchases;
4> drop SCHEMA Sales;
5> GO

Reference the schema in a query

4> --Create a schema
5> CREATE SCHEMA Sales
6> GO
1>
2>
3> --Create a table in the schema
4> CREATE TABLE Sales.SalesData
5> (
6>     SaleNumber INT,
7>     SaleDate DATETIME
8> )
9> GO
1>
2>
3> --
4> SELECT *
5> FROM Sales.SalesData
6> GO
SaleNumber  SaleDate
----------- -----------------------
(0 rows affected)
1>
2>
3> drop table Sales.SalesData;
4> GO
1>
2> drop SCHEMA Sales;
3> GO