metadata:有关数据的结构数据,即关于数据的信息
用sql定义一张表,表名、字段类型、外键约束等信息,都存储在数据库的系统表中。数据库系统表就是用来存储数据库元数据
-- ============================ 查看数据库表结构相关元数据 =================================
-- 1、【Object对象】包括表、视图、函数、存储过程,以及各种约束(默认值)、主外键、触发器、索引等
select * from sys.objects -- (937)系统自带与自定义对象
select * from sys.sysobjects -- (937)系统自带与自定义对象,对象个数与sys.objects相同,但是定义不同
select * from sys.system_objects -- (1935)系统对象,is_ms_shipped = 1
select * from sys.all_objects -- (2872)包括系统自带与自定义对象,个数最多
-- 【对象间的依赖关系】
select * from sys.sql_dependencies --包括字段的对象间的关联关系
-- (1)指定minor_id = 0可以保证是P、V、TF、FN、U之间的关系,而不是C与U之间的关系
-- (2)如果referenced_ser_name非空,表示远程服务器名称; 如果reference_database_name非空,表示外部数据库,同时referenced_id = NULL
select * from sys.sql_expression_dependencies --只有对象间的关联关系
-- 查看表属性,列信息继承于objects(每个用户表)
select * from sys.tables
-- 2、【表】
select * from sys.tables -- 自定义表
-- (1)【列】
select * from sys.columns -- 3794
select * from sys.system_columns -- 4666
select * from sys.syscolumns -- 3884
select * from sys.all_columns -- 个数是sys.columns与sys.system_columns之和
-- (2)【主键外键】
select * from sys.foreign_keys -- (sys.object.type = F)
select * from sys.sysforeignkeys
select * from sys.identity_columns
select * from sys.key_constraints -- 查看主键或唯一约束 sys.objects.type = PK | UQ
-- 查看外键与两个表间关系
select * from sys.foreign_key_columns
select * from sys.sysreferences
-- (3)【约束】
select * from sys.check_constraints -- type = C
select * from sys.default_constraints -- type = D
select * from sys.sysconstraints
-- (4)【索引】
select * from sys.sysindexes -- 特殊:objectId是所属表ID,name是PK,UQ的name
select * from sys.sysindexkeys -- 特殊:objectId是所属表ID
select OBJECT_NAME(t.object_id),* from sys.index_columns t
select OBJECT_NAME(t.object_id),* from sys.indexes t
-- (5)【触发器】
select * from sys.triggers -- (包括DDL和DML触发器)
select * from sys.trigger_events
select * from sys.trigger_event_types
-- 3、模块对象
-- 用SQL定义的模块对象
-- 类型为 P、RF、V、TR、FN、IF、TF 和 R 的对象均有关联的 SQL 模块。 在此视图中,独立的默认值,即 D 类型的对象也具有 SQL 模块定义
select OBJECT_NAME(object_id),* from sys.system_sql_modules -- 系统自带的模块对象(视图/存储过程/函数)的定义脚本
select OBJECT_NAME(object_id),* from sys.sql_modules -- 自定义的模块对象(视图/存储过程/函数)的定义脚本
-- (1)【视图】
select * from sys.views -- 全部是自定义的视图,(sys.objects.type = V)
select * from sys.system_views -- 全部是mssql内部创建的视图
select * from sys.all_views -- 包括sys.view与system_views,是它们的总和
-- (2)【存储过程】
select * from sys.procedures -- 自定义存储过程,(sys.objects.type = P|X|RF|PC)
-- 参数
select * from sys.system_parameters -- 系统对象的参数
select * from sys.parameters -- (自定义)函数与存储过程的参数(如是标量函数,parameter_id = 0是返回值参数,且name='')
select * from sys.all_parameters -- 包括parameters与system_parameters所有数据
-- syscomment获取默认值、脚本创建信息等
select OBJECT_NAME(id),* from syscomments
-- 【警告】:与sys.sql_modules类似,在修改存储过程、函数等对象名称时,将不触发
-- sys.sql_modules中definition/syscomments中text的创建脚本的修改,除非修改脚本内容
-- 解决办法:如创建脚本后修改名称,可通过删除对象后重新创建来保证对象与创建脚本中对象名称一致
-- 【备注信息】
select * from sys.extended_properties
-- 【class】
--0 = 数据库(DATABASE)
--1 = 对象或列(OBJECT_OR_COLUMN)
--2 = 参数(PARAMETER)
--3 = 架构(SCHEMA)
--4 = 数据库主体(DATABASE_PRINCIPAL)
--5 = 程序集(ASSEMBLY)
--6 = 类型(TYPE)
--7 = 索引(INDEX)
--10 = XML 架构集合(XML_SCHEMA_COLLECTION)
--15 = 消息类型(MESSAGE_TYPE)
--16 = 服务约定(SERVICE_CONTRACT)
--17 = 服务(SERVICE)
--18 = 远程服务绑定(REMOTE_SERVICE_BINDING)
--19 = 路由(ROUTE)
--20 = 数据空间(文件组或分区方案)(DATASPACE)
--21 = 分区函数(PARTITION_FUNCTION)
--22 = 数据库文件(DATABASE_FILE)
--27 = 计划指南(PLAN_GUIDE)
-- 【major_id】如果 class 为 0,则 major_id 始终为 0;如果 class 为 1、2 或 7,则 major_id 为 object_id;
-- 【minor_id】
-- 如果 class = 1,则 minor_id 在项为列的情况下等于 column_id;
-- 如果 class = 2,则 minor_id 为 parameter_id;
-- 如果 class = 7,则 minor_id 为 index_id
-- 其它(杂)
select * from sys.sysusers
select * from sys.systypes
select * from sys.types
-- 查看表、视图、存储过程、函数、主外键等(sys.objects不显示DDL触发器)
select * from sys.objects -- 同sys.sysobjects
-- 【principal_id描述】
--C = CHECK 约束
--D = DEFAULT(约束或独立)
--F = FOREIGN KEY 约束
--PK = PRIMARY KEY 约束
--R = 规则(旧式,独立)
--TA = 程序集(CLR 集成)触发器
--TR = SQL 触发器
--UQ = UNIQUE 约束
-- 【type】
--U = 表(用户定义类型)
--TR = SQL DML 触发器
--C = CHECK 约束
--D = DEFAULT(约束或独立)
--PK = PRIMARY KEY 约束
--F = FOREIGN KEY 约束
--UQ = UNIQUE 约束
--V = 视图
--P = SQL 存储过程
--AF = 聚合函数 (CLR)
--FN = SQL 标量函数
--TF = SQL 表值函数
--S = 系统基表
--X = 扩展存储过程
--FS = 程序集 (CLR) 标量函数
--FT = 程序集 (CLR) 表值函数
--PC = 程序集 (CLR) 存储过程
--TA = 程序集 (CLR) DML 触发器
--IF = SQL 内联表值函数
--IT = 内部表
--PG = 计划指南
--R = 规则(旧式,独立)
--RF = 复制筛选过程
--SN = 同义词
--SO = 序列对象(适用于2012以上版本)
--SQ = 服务队列
--TT = 表类型
--【sysobjects】
select * from sysobjects where xtype='U'
/*【更多xtype常用值:与sys.objects相同】
U:用户自定义表
S:系统表
P:存储过程
V:视图
TR:触发器
C:检查约束。
D:默认的约束
F:外键约束
L:日志
PK:主键约束
RF:复制过滤存储过程
UQ:独特的约束
X:被扩展的存储过程
*/
-- 【其它】
--获取所有数据库名称
select Name FROM Master..SysDatabases order by Name
-- 获取所有用户自定义表名
select Name FROM sysObjects Where XType='U' orDER BY Name
-- 获取表字段信息
Select * FROM SysColumns Where id=Object_Id('dbo.ams_biz_Leave')
-- 得到表中主键所包含的列名
SELECT syscolumns.name FROM syscolumns,sysobjects,sysindexes,sysindexkeys
WHERE syscolumns.id = object_id('dbo.ams_biz_Leave')
AND sysobjects.xtype = 'PK'
AND sysobjects.parent_obj = syscolumns.id
AND sysindexes.id = syscolumns.id
AND sysobjects.name = sysindexes.name
AND sysindexkeys.id = syscolumns.id
AND sysindexkeys.indid = sysindexes.indid
AND syscolumns.colid = sysindexkeys.colid
-- 获取表或视图的字段列表、类型、长度
-- OBJECT_ID(''),object_name 根据ID或Object名称相互获取
-- type_Id,type_name 根据类型ID与名称相互获取
-- COL_LENGTH,COL_NAME
-- Columnproperty,ObjectProperty(objectid,'propertyname'),获取属性值,其中的属性有哪些