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'