探索SQL Server元数据（二）

背景

　　上一篇中,我介绍了SQL Server 允许访问数据库的元数据，为什么有元数据，如何使用元数据。这一篇中我会介绍如何进一步找到各种有价值的信息。以触发器为例，因为它们往往一起很多问题。

那么如何找到触发器的数据?

　　以sys.system_viewsis表开始。让我们查询出数据库中使用触发器的信息。可以告知你当前SQL Server版本中有什么触发器。

SELECT schema_name(schema_ID)++FROM sys.system_views WHERE name LIKE '%trigger%’

sys.dm_exec_trigger_stats

sys.server_trigger_events

sys.server_triggers

sys.trigger_event_types

sys.trigger_events

sys.triggers

(6 row(s) affected)

　　其中sys.triggers看起来信息很多，它又包含什么列？下面这个查询很容易查到：

SELECT Thecol.name+ ++ CASE WHEN TheCol.is_nullable=1 THEN ' NULL’ ELSE ' NOT NULL’ END as Column_Information

FROM sys.system_views AS TheView

INNER JOIN sys.system_columns AS TheCol

ON TheView.object_ID</span>=<span style="color: #000000"&gt;TheCol.Object_ID

WHERE TheView.name = 'triggers'

ORDER BY column_ID;

结果如下:

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

因此我们多这个信息有了更好的理解，有了一个目录的目录。这个概念有点让人头晕，但是另一方面，它也是相当简单的。我们能够查出元数据，再找个查询中，需要做的就是改变这个单词‘triggers’来查找你想要的视图名称。.

在2012及其以后版本，可以使用一个新的表值函数极大地简化上述查询，并可以避免各种连接。在下面的查询中，我们将查找sys.triggers 视图 中的列。可以使用相同的查询通过更改字符串中的对象名称来获取任何视图的定义。

+ ++ CASE WHEN is_nullable=1 THEN ' NULL’ ELSE ' NOT NULL’ END as Column_Information

FROM sys.dm_exec_describe_first_result_set

( N'SELECT * FROM sys.triggers;’,NULL,0) AS f

ORDER BY column_ordinal;

查询结果如下:

—————————————-

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

sys.dm_exec_describe_first_result_set函数的最大优势在于你能看到任何结果的列，不仅仅是表和视图、存储过程或者贬值函数。

为了查出任何列的信息，你可以使用稍微修改的版本，只需要改变代码中的字符串’sys.triggers’即可，如下：

Declare @TheParamater nvarchar(Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ’ + @TheParamater

SELECT

name+ ’ ’+ system_type_name

+ CASE WHEN is_nullable=1 THEN ' NULL’ ELSE ' NOT NULL’ END as Column_Information

FROM sys.dm_exec_describe_first_result_set

( @TheParamater,0) AS f

ORDER BY column_ordinal;

但是当然一个触发器是首先是一个对象，因此一定在sys.objects?

　　在我们使用sys.triggers的信息之前，需要来重复一遍，所有的数据库对象都存在于sys.objects中，在SQL Server 中的对象包括以下：聚合的CLR函数，check 约束，SQL标量函数，CLR标量函数,CLR表值函数，SQL内联表值函数，内部表，SQL存储过程，CLR存储过程，计划指南，主键约束，老式规则，复制过滤程序，系统基础表，同义词，序列对象，服务队列，CLR DML 触发器，SQL表值函数，表类型，用户自定义表，唯一约束，视图和扩展存储过程等。

　　触发器是对象所以基础信息一定保存在sys.objects。不走运的是，有时我们需要额外的信息，这些信息可以通过目录视图查询。这些额外数据有是什么呢？

　　修改我们使用过的查询，来查询sys.triggers的列，这次我们会看到额外信息。这些额外列是来自于sys.objects。

SELECT coalesce(trigger_column.name,FROM

(SELECT Thecol.name

FROM sys.system_views AS TheView

INNER JOIN sys.system_columns AS TheCol

  ON TheView.object_ID</span>=<span style="color: #000000"&gt;TheCol.Object_ID

WHERE TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

(SELECT Thecol.name

FROM sys.system_views AS TheView

INNER JOIN sys.system_columns AS TheCol

  ON TheView.object_ID</span>=<span style="color: #000000"&gt;TheCol.Object_ID

WHERE TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果：

In_Sys_Triggers In_Sys_Objects

—————————— ———————-

name name

object_id object_id

NOT INCLUDED principal_id

NOT INCLUDED schema_id

NOT INCLUDED parent_object_id

type type

type_desc type_desc

create_date create_date

modify_date modify_date

is_ms_shipped is_ms_shipped

NOT INCLUDED is_published

NOT INCLUDED is_schema_published

is_not_for_replication NOT INCLUDED

is_instead_of_trigger NOT INCLUDED

parent_id NOT INCLUDED

is_disabled NOT INCLUDED

parent_class NOT INCLUDED

parent_class_desc NOT INCLUDED

丽水站长网

探索SQL Server元数据（二）

背景

那么如何找到触发器的数据?

但是当然一个触发器是首先是一个对象，因此一定在sys.objects?

dawei

背景

那么如何找到触发器的数据?

但是当然一个触发器是首先是一个对象，因此一定在sys.objects?

相关文章