sql总结之数据库的元数据

最新推荐文章于 2023-05-24 09:58:10 发布

刘德华的刚

最新推荐文章于 2023-05-24 09:58:10 发布

阅读量2.8k

点赞数

                        <!-- flowchart 箭头图标 勿删 -->
                        <svg xmlns="http://www.w3.org/2000/svg" style="display: none;"><path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path></svg>
                        <h2 id="1数据库元数据"><a name="t0"></a>1、数据库元数据</h2>

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

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192

(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; --查看数据库
 
 1
2
3
4
5
6
7
8
9
10

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
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

(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;

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

(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');
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

(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

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

(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

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

(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
 
 1
2
3
4
5
6
7
8
9
10
11
12

(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;
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

刘德华的刚

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
sql总结之数据库的元数据

&lt;!-- flowchart 箭头图标勿删 --&gt; &lt;svg xmlns="http://www.w3.org/2000/svg" style="display: none;"&gt;&lt;path stroke-linecap="round" d="M5,0 0,2.5 5,5z.
复制链接

扫一扫