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

Материал из SQL эксперт
Перейти к: навигация, поиск

Create a new schema

   <source lang="sql">

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</source>


Move tables between schemas

   <source lang="sql">

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</source>


Reference the schema in a query

   <source lang="sql">

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</source>