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)时将会在它前面增加一个字符'
'
-- 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,''),'
','') +
-- 主键、唯一约束列表
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('')),'
',''),'') +
-- 外键
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('')),'
',''),'') +
-- 检查约束
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('')),'
',''),'') +
+ 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,''),'
','') +
-- 表主键、唯一约束列表
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('')),'
',''),'') +
-- 外键
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('')),'
',''),'') +
-- 检查约束
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('')),'
',''),'') +
+ 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;
扫一扫
9298
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
