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

(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 a
left join sys.types b on a.user_type_id=b.user_type_id
inner 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.ID
left 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.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
ORDER 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_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE 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]
AS
BEGIN
    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 MyCursor
END

-- 方法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_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 ' 
            -- 引用表
            + 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 remark
from sys.objects t
left join sys.objects t2 on t2.object_id = t.parent_object_id
left 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|UQ
left join sys.foreign_keys k on k.object_id = t.object_id   -- FK
left 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   -- D
left 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_id
union   -- U与U的关系(外键)
select distinct parent_object_id,referenced_object_id from sys.foreign_keys where parent_object_id <> referenced_object_id
union   -- 表内部对象(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_date
FROM sys.objects
WHERE modify_date > GETDATE() - 3
ORDER 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_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER 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;
  • 8
    点赞
  • 15
    收藏
  • 打赏
    打赏
  • 3
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论 3

打赏作者

gezhonglei2007

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值