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'
posted @ 2015-03-18 22:58  Haseo  阅读(642)  评论(0编辑  收藏  举报