metadata---元数据

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')，获取属性值，其中的属性有哪些