This website requires JavaScript.

MSSQL: database schema

数据中有4个Schema无法被删除

● dbo, 具有db_owner或者db_ddl_admin 的用户,新创建对象默认schema就是dbo

● guest , 用来给guest 用户使用,这个schema很少用到

● INFORMATION_SCHEMA , 由Information Schema views使用,提供metadata查询。

● sys , 系统对象的所使用的schema

创建schema

CREATE SCHEMA Production AUTHORIZATION dbo;
**转移schema**
-- The following statement moves the Production.Categories table to the Sales database schema:
ALTER SCHEMA Sales TRANSFER Production.Categories;
-- To move the table back, issue:
ALTER SCHEMA Production TRANSFER Sales.Categories;
**schema 信息查询**
SELECT TABLE_CATALOG, TABLE_SCHEMA, 
    TABLE_NAME, TABLE_TYPE
FROM    INFORMATION_SCHEMA.TABLES;

SELECT VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME,     TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,     COLUMN_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE COLUMN_NAME = 'BusinessEntityID'
 
0条评论
avatar