SQL总结之数据库元数据（MSSQL）

1、数据库元数据

(1) 什么是数据元数据？

元数据，是指定义数据结构的数据。那么数据库元数据就是指定义数据库各类对象结构的数据。
例如用sql定义一张表，表名、字段类型、外键约束等信息，都存储在数据库的系统表中。这里数据库系统表就是用来存储数据库元数据的。

常见的数据库对象，包括：

• 数据库表
• 触发器
• 索引
• 视图
• 存储过程
• 函数

掌握数据库元数据，就需要深入地理解数据库的结构组成。

（2）那么掌握数据库元数据有什么用呢？

一方面，在应用设计时能够充分地利用数据库元数据。许多应用系统总是自定义一些辅助的字典表，辅助功能设计。数据库元数据提供信息已经够全面，如果足够你的需求，根本不用另外设计字典表。

另一方面，深入理解了数据库组织结构，再去理解数据访问相关框架的实现原理会更加容易。

2、获取数据库元数据

(1) 数据库对象相关系统表、视图

-- ============================ 查看数据库表结构相关元数据 =================================-- 1、【Object对象】包括表、视图、函数、存储过程，以及各种约束（默认值）、主外键、触发器、索引等select * from sys.objects        -- (937)系统自带与自定义对象select * from sys.sysobjects     -- (937)系统自带与自定义对象,对象个数与sys.objects相同,但是定义不同select * from sys.system_objects -- (1935)系统对象,is_ms_shipped = 1select * 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 = NULLselect * from sys.sql_expression_dependencies   --只有对象间的关联关系-- 查看表属性，列信息继承于objects(每个用户表)select * from sys.tables-- 2、【表】select * from sys.tables        -- 自定义表-- (1)【列】select * from sys.columns               -- 3794select * from sys.system_columns        -- 4666select * from sys.syscolumns            -- 3884select * from sys.all_columns           -- 个数是sys.columns与sys.system_columns之和-- (2)【主键外键】select * from sys.foreign_keys      -- (sys.object.type = F)select * from sys.sysforeignkeysselect * from sys.identity_columnsselect * from sys.key_constraints --  查看主键或唯一约束 sys.objects.type = PK | UQ-- 查看外键与两个表间关系select * from sys.foreign_key_columnsselect * from sys.sysreferences-- (3)【约束】select * from sys.check_constraints     -- type = Cselect * from sys.default_constraints   -- type = Dselect * from sys.sysconstraints-- (4)【索引】select * from sys.sysindexes        -- 特殊：objectId是所属表ID,name是PK,UQ的nameselect * from sys.sysindexkeys      -- 特殊：objectId是所属表IDselect OBJECT_NAME(t.object_id),* from sys.index_columns tselect OBJECT_NAME(t.object_id),* from sys.indexes t-- (5)【触发器】select * from sys.triggers          -- (包括DDL和DML触发器)select * from sys.trigger_eventsselect * 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.sysusersselect * from sys.systypesselect * 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')，获取属性值，其中的属性有哪些

(2) 查看数据库对象的相关存储过程

-- 目录存储过程EXEC sp_tables;                 -- 查看查询对象的列表EXEC sp_table_privileges 'Uv_%' -- 查看查询对象的权限EXEC sp_stored_procedures;      -- 当前数据库中的所有存储过程EXEC sp_sproc_columns '%','dbo' -- 查看存储过程的参数列表EXEC sp_pkeys @table_name = N'cms_biz_SalaryItem'               -- 单个表的主键信息EXEC sp_fkeys @pktable_name = N'cms_biz_SalaryItem'             -- 逻辑外键信息EXEC sp_columns @table_name = N'cms_biz_SalaryItem'             -- 指定表的列信息EXEC sp_column_privileges @table_name = 'cms_biz_SalaryItem'    -- 列权限信息EXEC sp_databases; --查看数据库

3、数据库元数据的综合应用

(1) 查看数据库所有表结构元数据信息

-- ============================ 1、查看数据库所有表结构元数据信息 =========================-- 2005实现字段属性统计(2000里的系统表sysproperties描述表、字段不存在，2005里用sys.extended_properties视图替代)select     [表名]=c.Name,    [表说明]=isnull(f.[value],''),    [列名]=a.Name,    [列序号]=a.Column_id,    [标识]=case when is_identity=1 then '√' else '' end,    [主键]=case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in        (select Name from sys.indexes where index_id in        (select indid from sysindexkeys where ID=a.object_id and colid=a.column_id)))        then '√' else '' end,    [类型]=b.Name,    [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'         when b.Name='xml' then ' 2^31-1字节/2G'        else rtrim(a.[max_length]) end,    [长度]=ColumnProperty(a.object_id,a.Name,'Precision'),    [小数]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),    [是否为空]=case when a.is_nullable=1 then '√' else '' end,    [列说明]=isnull(e.[value],''),    [默认值]=isnull(d.text,'') from sys.columns aleft join sys.types b on a.user_type_id=b.user_type_idinner join sys.objects c on a.object_id=c.object_id and c.Type='U'left join sys.syscomments d on a.default_object_id=d.IDleft join sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 left join sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1

(2) 获取创建存储过程、函数、视图的创建脚本

-- ====================== 2、获取创建存储过程、函数、视图的创建脚本 =====================-- 1、查看定义脚本SELECT NAME, RTRIM(cmmt.[text])FROM dbo.sysobjects obj    LEFT JOIN dbo.syscomments cmmt on cmmt.id = OBJECT_ID(obj.name)    WHERE NAME NOT IN ('SYSCONSTRAINTS','SYSSEGMENTS')        AND        (            OBJECTPROPERTY(obj.id, N'IsView') = 1               --视图            OR OBJECTPROPERTY(obj.id,N'IsProcedure') = 1        --存储过程            OR OBJECTPROPERTY(obj.id,N'IsScalarFunction') = 1   --标量函数            OR OBJECTPROPERTY(obj.id,N'IsTableFunction') = 1    --标题函数            OR OBJECTPROPERTY(obj.id,N'IsInlineFunction') = 1   --内联函数        );-- 2、当前数据库中每个模块的名称、类型和定义SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definitionFROM sys.sql_modules AS smJOIN sys.objects AS o ON sm.object_id = o.object_idORDER BY o.type;-- 3、查看定义对象(存储过程/视图/函数)的文本EXEC sp_helptext 'dbo.fun_CMSReplaceQuote'EXEC sp_helptext 'dbo.Usp_CMS_UpdateSalaryEmployee'EXEC sp_helptext 'dbo.Uv_CMSSalarySetItem'-- 4、返回指定存储过程或函数的参数SELECT SCHEMA_NAME(schema_id) AS schema_name    ,o.name AS object_name    ,o.type_desc    ,p.parameter_id    ,p.name AS parameter_name    ,TYPE_NAME(p.user_type_id) AS parameter_type    ,p.max_length    ,p.precision    ,p.scale    ,p.is_outputFROM sys.objects AS oINNER JOIN sys.parameters AS p ON o.object_id = p.object_idWHERE o.object_id = OBJECT_ID('dbo.fun_CMSReplaceQuote')ORDER BY schema_name, object_name, p.parameter_id;

(3) 修改表结构后批量更新所有视图

视图是基于表创建的，如果表结构有更新（增删字段或修改字段类型），但是视图不更新时调用视图时会报错，尽管表结构的更改于创建视图的SQL没有任何改变。

尽管sql server 2008提供自动刷新视图的存储过程sp_refreshview，当然只是针对表结构修改不影响视图创建SQL的情况，如果对视图创建SQL有影响的表结构修改，仍然是需要手动来处理的。

在这里我们需要做的是找出所有与此表相关的视图。

-- ====================== 3、SQL Server修改表结构后批量更新所有视图 ===========================-- 方法1、创建存储过程CREATE PROCEDURE [dbo].[RefreshAllView]ASBEGIN    DECLARE MyCursor CURSOR    FOR select Name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsView') = 1 and (not name in ('sysconstraints','syssegments'))    DECLARE @name varchar(40)    OPEN MyCursor    FETCH NEXT FROM MyCursor INTO @name    WHILE (@@fetch_status <> -1)    BEGIN     IF (@@fetch_status <> -2)     begin        exec sp_refreshview @name     end     FETCH NEXT FROM MyCursor INTO @name    END    CLOSE MyCursor    DEALLOCATE MyCursorEND-- 方法2: 更新指定对象相关的所有视图SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''' FROM sys.objects AS so INNER JOIN sys.sql_expression_dependencies AS sed     ON so.object_id = sed.referencing_id WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('Uv_viewname');

(4) 查看创建表的SQL脚本

不像函数、存储过程、视图那样，系统表会直接存储脚本，表结构是完全结构化存储的，需要自己从系统表中获取表、字段等信息拼接而成。

-- ========================= 表创建SQL ============================-- 字符串转xml时，遇到换行符 CHAR(13)+CHAR(10)时将会在它前面增加一个字符'&#x0D;'-- CHAR(13)->回车,CHAR(10)->换行符,CHAR(9)->制表符同tab键select t.object_id,t.name,'CREATE TABLE ' + QUOTENAME(t.name) + ' (' +-- 字段列表REPLACE(STUFF((select ',' + CHAR(13) + CHAR(10) + CHAR(9) + col.name + ' ' +    (case when tp.name in ('decimal','numeric') then tp.name + '(' + cast(col.precision as varchar) + ',' + cast(col.scale as varchar) + ')'        when tp.name in ('float') and col.precision <> tp.precision then tp.name + '(' + cast(col.precision as varchar) + ')'        when tp.name in ('binary','char') and col.max_length <> 1 then tp.name + '(' + cast(col.max_length as varchar) + ')'        when tp.name in ('varbinary','varchar') and col.max_length <> 1 then tp.name + '(' + (case when col.max_length = -1 then 'max' else cast(col.max_length as varchar) end) + ')'        when tp.name in ('nchar','nvarchar') and col.max_length <> 1 then tp.name + '(' + (case when col.max_length = -1 then 'max' else cast(col.max_length/2 as varchar) end) + ')'        else tp.name end) +     isnull(' ' +col.collation_name,'') +     (case when col.is_nullable = 1 then '' else ' NOT NULL' end) +    (case when col.is_identity = 1 then ' IDENTITY' + (case when seed_value = 1 and increment_value = 1 then '' else ' ' + cast(seed_value as varchar) + ',' + cast(increment_value as varchar) + ')' end)  else '' end) +    (case when col.default_object_id <> 0 then ' DEFAULT ' + df.definition else '' end) +    (case when col.is_sparse = 1 then ' SPARSE' else '' end +    -- Column_Set    (case when col.is_column_set = 1 then ' XML COLUMN_SET FOR ALL_SPARSE_COLUMNS' else '' end) +    -- computed_column    (case when col.is_computed = 1 then ' AS ' + cmp.definition else '' end))from sys.columns col    left join sys.types tp on tp.user_type_id = col.user_type_id    left join sys.identity_columns iden on iden.object_id = col.object_id and iden.column_id = col.column_id    left join sys.computed_columns cmp on cmp.object_id = col.object_id and cmp.column_id = col.column_id    left join sys.default_constraints df on df.object_id = col.default_object_idwhere col.object_id = t.object_id for xml path('')),1,1,''),'&#x0D;','') + -- 主键、唯一约束列表ISNULL(REPLACE((select ',' + CHAR(13) + CHAR(10) + CHAR(9) + 'CONSTRAINT ' + ind.name + ' ' +            (case when o.type in('PK') then 'PRIMARY KEY' else 'UNIQUE' end) + ' (' +            STUFF((select ',' + col.name from sys.index_columns indcol                        left join sys.columns col on col.object_id = ind.object_id and col.column_id = indcol.column_id                    where indcol.object_id = ind.object_id and indcol.index_id = ind.index_id                     for xml path('')),1,1,'') + ')'    from sys.indexes ind    left join sys.objects o on o.name = ind.name    where o.type in('PK','UQ') and ind.object_id = t.object_id for xml path('')),'&#x0D;',''),'') +-- 外键ISNULL(REPLACE((select ',' + CHAR(13) + CHAR(10) + CHAR(9) + 'CONSTRAINT ' + k.name + ' FOREIGN KEY (' +            STUFF((select ',' + col.name                    from sys.foreign_key_columns kcol                        left join sys.columns col on col.object_id = kcol.parent_object_id and col.column_id = kcol.parent_column_id                    where t.type in('F') and kcol.constraint_object_id = k.object_id                    for xml path('')),1,1,'') + ') REFERENCES '             -- 引用表            + OBJECT_NAME(k.parent_object_id) + ' ('            -- 引用表字段(用','分隔)            + STUFF((select ',' + rcol.name                    from sys.foreign_key_columns kcol                        left join sys.columns rcol on rcol.object_id = kcol.referenced_object_id and rcol.column_id = kcol.referenced_column_id                        where t.type in('F') and kcol.constraint_object_id = k.object_id                    for xml path('')),1,1,'') + ') '             -- 下面出现collation conflict需要指定字符集            --+ (case when k.delete_referential_action = 0 then '' else ' ON DELETE ' + k.delete_referential_action_desc collate Chinese_PRC_CI_AS end)            --+ (case when k.update_referential_action = 0 then '' else ' ON UPDATE ' + k.update_referential_action_desc collate Chinese_PRC_CI_AS end)            + (case when k.delete_referential_action = 1 then ' ON DELETE CASCADE'                     when k.delete_referential_action = 2 then ' ON DELETE SET_NULL'                    when k.delete_referential_action = 3 then ' ON DELETE SET_DEFAULT' else '' end)            + (case when k.update_referential_action = 1 then ' ON UPDATE CASCADE'                     when k.update_referential_action = 2 then ' ON UPDATE SET_NULL'                    when k.update_referential_action = 3 then ' ON UPDATE SET_DEFAULT' else '' end)            from sys.foreign_keys k             where k.parent_object_id = t.object_id for xml path('')),'&#x0D;',''),'') +-- 检查约束ISNULL(REPLACE((select ',' + CHAR(13) + CHAR(10) + CHAR(9) + 'CONSTRAINT ' + ckcons.name + ' CHECK '             + (case when ckcons.is_not_for_replication = 1 then ' NOT FOR REPLICATION' else '' end)             + ckcons.definition            from sys.check_constraints ckcons            where ckcons.parent_object_id = t.object_id for xml path('')),'&#x0D;',''),'') ++ CHAR(13) + CHAR(10) + ')'from sys.tables t

(4) 查看数据库所有对象的创建脚本

就是结合(2)和(3)查询数据库所有对象的创建脚本

-- 4、查看所有对象的创建脚本select t.type,t.object_id as objectid,t.name,    t.parent_object_id as pobjectid,t2.name as pname,    case when t.type in('P','V','TR','FN','TF') then m.definition        when t.type in('PK','UQ') then (case when t.type in('PK') then 'PRIMARY KEY' else 'UNIQUE' end) + ' (' +            STUFF((select ',' + col.name                    from sys.index_columns indcol                        left join sys.columns col on col.object_id = t.parent_object_id and col.column_id = indcol.column_id                    where t.type in('PK','UQ') and indcol.object_id = ind.object_id and indcol.index_id = ind.index_id                     for xml path('')),1,1,'') + ')'        when t.type in('F') then ('FOREIGN KEY (' +            -- 外键字段(用','分隔)            STUFF((select ',' + col.name                    from sys.foreign_key_columns kcol                        left join sys.columns col on col.object_id = kcol.parent_object_id and col.column_id = kcol.parent_column_id                    where t.type in('F') and kcol.constraint_object_id = k.object_id                    for xml path('')),1,1,'') + ') REFERENCES '             -- 引用表            + t.name + ' ('            -- 引用表字段(用','分隔)            + STUFF((select ',' + rcol.name                    from sys.foreign_key_columns kcol                        left join sys.columns rcol on rcol.object_id = kcol.referenced_object_id and rcol.column_id = kcol.referenced_column_id                        where t.type in('F') and kcol.constraint_object_id = k.object_id                    for xml path('')),1,1,'') + ') '             -- 下面出现collation conflict为什么？            --+ (case when k.delete_referential_action = 0 then '' else ' ' + k.delete_referential_action_desc end)            --+ (case when k.update_referential_action = 0 then '' else ' ' + k.update_referential_action_desc end)            + (case when k.delete_referential_action = 1 then ' ON DELETE CASCADE'                     when k.delete_referential_action = 2 then ' ON DELETE SET_NULL'                    when k.delete_referential_action = 3 then ' ON DELETE SET_DEFAULT' else '' end)            + (case when k.update_referential_action = 1 then ' ON UPDATE CASCADE'                     when k.update_referential_action = 2 then ' ON UPDATE SET_NULL'                    when k.update_referential_action = 3 then ' ON UPDATE SET_DEFAULT' else '' end)            + (case when k.is_not_for_replication = 1 then ' NOT FOR REPLICATION' else '' end))        when t.type in('D') then 'DEFAULT ' + dcons.definition + ' FOR ' + dcons.colname        when t.type in('C') then 'CHECK ' + (case when ckcons.is_not_for_replication = 1 then ' NOT FOR REPLICATION' else '' end)             + ckcons.definition        when t.type in('U') then 'CREATE TABLE ' + QUOTENAME(t.name) + ' (' +            -- 表字段列表            REPLACE(STUFF((select ',' + CHAR(13) + CHAR(10) + CHAR(9) + col.name + ' ' +                (case when tp.name in ('decimal','numeric') then tp.name + '(' + cast(col.precision as varchar) + ',' + cast(col.scale as varchar) + ')'                    when tp.name in ('float') and col.precision <> tp.precision then tp.name + '(' + cast(col.precision as varchar) + ')'                    when tp.name in ('binary','char') and col.max_length <> 1 then tp.name + '(' + cast(col.max_length as varchar) + ')'                    when tp.name in ('varbinary','varchar') and col.max_length <> 1 then tp.name + '(' + (case when col.max_length = -1 then 'max' else cast(col.max_length as varchar) end) + ')'                    when tp.name in ('nchar','nvarchar') and col.max_length <> 1 then tp.name + '(' + (case when col.max_length = -1 then 'max' else cast(col.max_length/2 as varchar) end) + ')'                    else tp.name end) +                 isnull(' ' +col.collation_name,'') +                 (case when col.is_nullable = 1 then '' else ' NOT NULL' end) +                (case when col.is_identity = 1 then ' IDENTITY' + (case when seed_value = 1 and increment_value = 1 then '' else ' ' + cast(seed_value as varchar) + ',' + cast(increment_value as varchar) + ')' end)  else '' end) +                (case when col.default_object_id <> 0 then ' DEFAULT ' + df.definition else '' end) +                (case when col.is_sparse = 1 then ' SPARSE' else '' end +                -- Column_Set                (case when col.is_column_set = 1 then ' XML COLUMN_SET FOR ALL_SPARSE_COLUMNS' else '' end) +                -- computed_column                (case when col.is_computed = 1 then ' AS ' + cmp.definition else '' end))            from sys.columns col                left join sys.types tp on tp.user_type_id = col.user_type_id                left join sys.identity_columns iden on iden.object_id = col.object_id and iden.column_id = col.column_id                left join sys.computed_columns cmp on cmp.object_id = col.object_id and cmp.column_id = col.column_id                left join sys.default_constraints df on df.object_id = col.default_object_id            where col.object_id = t.object_id for xml path('')),1,1,''),'&#x0D;','') +             -- 表主键、唯一约束列表            ISNULL(REPLACE((select ',' + CHAR(13) + CHAR(10) + CHAR(9) + 'CONSTRAINT ' + ind.name + ' ' +                        (case when o.type in('PK') then 'PRIMARY KEY' else 'UNIQUE' end) + ' (' +                        STUFF((select ',' + col.name from sys.index_columns indcol                                    left join sys.columns col on col.object_id = ind.object_id and col.column_id = indcol.column_id                                where indcol.object_id = ind.object_id and indcol.index_id = ind.index_id                                 for xml path('')),1,1,'') + ')'                from sys.indexes ind                left join sys.objects o on o.name = ind.name                where o.type in('PK','UQ') and ind.object_id = t.object_id for xml path('')),'&#x0D;',''),'') +            -- 外键            ISNULL(REPLACE((select ',' + CHAR(13) + CHAR(10) + CHAR(9) + 'CONSTRAINT ' + k.name + ' FOREIGN KEY (' +                        STUFF((select ',' + col.name                                from sys.foreign_key_columns kcol                                    left join sys.columns col on col.object_id = kcol.parent_object_id and col.column_id = kcol.parent_column_id                                where t.type in('F') and kcol.constraint_object_id = k.object_id                                for xml path('')),1,1,'') + ') REFERENCES '                         -- 引用表                        + t.name + ' ('                        -- 引用表字段(用','分隔)                        + STUFF((select ',' + rcol.name                                from sys.foreign_key_columns kcol                                    left join sys.columns rcol on rcol.object_id = kcol.referenced_object_id and rcol.column_id = kcol.referenced_column_id                                    where t.type in('F') and kcol.constraint_object_id = k.object_id                                for xml path('')),1,1,'') + ') '                         -- 下面出现collation conflict为什么？                        --+ (case when k.delete_referential_action = 0 then '' else ' ' + k.delete_referential_action_desc end)                        --+ (case when k.update_referential_action = 0 then '' else ' ' + k.update_referential_action_desc end)                        + (case when k.delete_referential_action = 1 then ' ON DELETE CASCADE'                                 when k.delete_referential_action = 2 then ' ON DELETE SET_NULL'                                when k.delete_referential_action = 3 then ' ON DELETE SET_DEFAULT' else '' end)                        + (case when k.update_referential_action = 1 then ' ON UPDATE CASCADE'                                 when k.update_referential_action = 2 then ' ON UPDATE SET_NULL'                                when k.update_referential_action = 3 then ' ON UPDATE SET_DEFAULT' else '' end)                        from sys.foreign_keys k                         where k.parent_object_id = t.object_id for xml path('')),'&#x0D;',''),'') +            -- 检查约束            ISNULL(REPLACE((select ',' + CHAR(13) + CHAR(10) + CHAR(9) + 'CONSTRAINT ' + ckcons.name + ' CHECK '                         + (case when ckcons.is_not_for_replication = 1 then ' NOT FOR REPLICATION' else '' end)                         + ckcons.definition                        from sys.check_constraints ckcons                        where ckcons.parent_object_id = t.object_id for xml path('')),'&#x0D;',''),'') +            + CHAR(13) + CHAR(10) + ')'        else t.name end as Expression,        case when t.type in('U') then isnull(cast(pro.value as varchar),'') else '' end as remarkfrom sys.objects tleft join sys.objects t2 on t2.object_id = t.parent_object_idleft join sys.sql_modules m on m.object_id = t.object_id    -- P|V|TR|FN|TF /*AF聚合函数不可创建*/left join sys.indexes ind on ind.name = t.name and ind.object_id = t.parent_object_id   -- PK|UQleft join sys.foreign_keys k on k.object_id = t.object_id   -- FKleft join (select cons.object_id,cons.definition,col.name as colname    from sys.default_constraints cons    left join sys.columns col on col.object_id = cons.parent_object_id         and col.column_id = cons.parent_column_id) dcons on dcons.object_id = t.object_id   -- Dleft join (select cons.object_id,definition,cons.is_not_for_replication    from sys.check_constraints cons) ckcons on ckcons.object_id = t.object_id-- 视图备注信息(自动生成)可能出现多条数据导致数据翻倍left join sys.extended_properties pro on pro.major_id = t.object_id and pro.minor_id = 0 and class = 1 and t.type = 'U'where t.type in('V','P','FN','TF','AF','TR','U','PK','F','UQ','D','C')order by type

(5) 查看数据库对象依赖关系

数据库对象的依赖关系，主要包括

• 表与表之间的外键
• 存储过程、函数之间的引用关系
• 表内部对象（触发器、约束）与表之间的关联关系

-- 5、查看对象间的依赖关系--  (1)指定minor_id = 0可以保证是P、V、TF、FN、U之间的关系，而不是C与U之间的关系--  (2)如果referenced_ser_name非空，表示远程服务器名称; 如果reference_database_name非空，表示外部数据库，同时referenced_id = NULL--  (3)排除对象之间的自身引用关系select *from(   -- P、V、TF、FN、U之间的关系select distinct referencing_id as ReferId,referenced_id as ReferedId from sys.sql_expression_dependencies where referencing_minor_id = 0 and referenced_minor_id = 0 and referenced_id is not null    and referencing_id <> referenced_idunion   -- U与U的关系（外键）select distinct parent_object_id,referenced_object_id from sys.foreign_keys where parent_object_id <> referenced_object_idunion   -- 表内部对象(TR,PK,F,UQ,D,C)与表之间的关系select object_id,parent_object_id from sys.objects where type in('TR','PK','F','UQ','D','C')) t

(6) 其它

-- =======================================================================================-- 返回在最近 N 天内修改过的所有对象SELECT name AS object_name   ,SCHEMA_NAME(schema_id) AS schema_name  ,type_desc  ,create_date  ,modify_dateFROM sys.objectsWHERE modify_date > GETDATE() - 3ORDER BY modify_date;-- MSSQL查看一个数据库中所有表的行数 SELECT   a.name, b.rows FROM sysobjects AS a     INNER JOIN sysindexes AS b ON a.id = b.id WHERE (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY b.rows DESC -- 获取有关按平均 CPU 时间排在最前面的五个查询的信息SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,         ((CASE qs.statement_end_offset          WHEN -1 THEN DATALENGTH(st.text)         ELSE qs.statement_end_offset         END - qs.statement_start_offset)/2) + 1) AS statement_textFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stORDER BY total_worker_time/execution_count DESC;-- 提供批处理执行统计信息SELECT s2.dbid,     s1.sql_handle,      (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,       ( (CASE WHEN statement_end_offset = -1          THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)          ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,    execution_count,     plan_generation_num,     last_execution_time,       total_worker_time,     last_worker_time,     min_worker_time,     max_worker_time,    total_physical_reads,     last_physical_reads,     min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writes  FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;