深究SQL Server元数据(二)

背景

  上一篇中,我介绍了SQL Server
允许访问数据库的元数据,为啥有元数据,怎样使用元数据。这一篇中我会介绍咋样进一步找到各个有价值的音讯。以触发器为例,因为它们往往一起很多题目。

 

这就是说如何找到触发器的数目?

*  以sys.system_views*is表开首。让我们查询出数据库中应用触发器的音讯。可以告知您眼前SQL
Server版本中有咋样触发器。

SELECT schema_name(schema_ID)+'.'+ name

  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+ ' '+ Type_name(TheCol.system_type_id)

  + 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=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

----------------------------------------

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
视图

中的列。可以运用同样的查询通过更改字符串中的对象名称来博取此外视图的概念。

 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

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

  ORDER BY column_ordinal;

询问结果如下:

 Column_Information

----------------------------------------

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(255)

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, NULL, 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,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=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=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

 

上述这个让大家领会在sys.triggers的额外信息,可是因为它始终是表的子对象,所以有些不相干音讯是不会来得在这些指定的视图或者sys.triggers中的。现在快要带我们去继续找找这么些信息。

触发器的题材

  触发器是行之有效的,不过因为它们在SSMS对象资源管理器窗格中不是可见的,所以一般用来唤起错误。触发器有时候会有些微妙的地点让其出题目,比如,当导入过程中禁用了触发器,并且鉴于一些原因他们没有重启。

上边是一个关于触发器的概括指示:

  触发器能够在视图,表或者服务器上,任何这几个目标上都足以有跨越1个触发器。普通的DML触发器能被定义来实施代表一些多少修改(Insert,Update或者Delete)或者在数量修改之后执行。每一个触发器与只与一个对象管理。DDL触发器与数据库关联或者被定义在服务器级别,这类触发器一般在Create,Alter或者Drop那类SQL语句执行后触发。

  像DML触发器一样,可以有四个DDL触发器被成立在同一个T-SQL语句上。一个DDL触发器和讲话触发它的说话在同一个事情中运行,所以除了Alter
DATABASE之外都足以被回滚。DDL触发器运行在T-SQL语句执行完毕后,也就是不可以当做Instead
OF触发器使用。

  二种触发器都与事件相关,在DML触发器中,包含INSERT, UPDATE,
和DELETE,可是无数风波都可以与DDL触发器关联,稍后我们将精晓。

在数据库中列出触发器

那么怎么获取触发器列表?下边我在AdventureWorks数据库中开展询问,注意该库的视图中尚无触发器。

先是个查询所有消息都在sys.triggers 的目录视图中。

SELECT

  name AS TriggerName,

  coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')') AS TheParent

FROM sys.triggers;



TriggerName                    TheParent

------------------------------ ----------------------------------------

ddlDatabaseTriggerLog          Database (AdventureWorks2012)          

dEmployee                      HumanResources.Employee                

iuPerson                       Person.Person                          

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader         

iduSalesOrderDetail            Sales.SalesOrderDetail                 

uSalesOrderHeader              Sales.SalesOrderHeader                 

dVendor                        Purchasing.Vendor                      

iWorkOrder                     Production.WorkOrder                   

uWorkOrder                     Production.WorkOrder   

  我使用元数据函数db_name()使SQL保持简单。db_name()告诉我数据库的称号。object_schema_name()用来查询object_ID意味着的对象的架构,以及object_name**()**查询对象名称。这么些对目的的引用指向触发器的主人,触发器可以是数据库本身,也得以是表:服务器触发器有谈得来的序列视图,稍后我会显示。

万一想要看到有着触发器,那么我们最好利用sys.objects 视图:

SELECT name as TriggerName, object_schema_name(parent_object_ID)+'.'

    +object_name(parent_object_ID) AS TheParent

            FROM   sys.objects

           WHERE  OBJECTPROPERTYEX(object_id,'IsTrigger') = 1

 

留意,输出不含有数据库级此外触发器,因为有着的DML触发器都在sys.objects视图中,不过你会挂一漏万在sys.triggers视图中的触发器。

地点查询结果:

name                           TheParent

------------------------------ -------------------------------

dEmployee                      HumanResources.Employee

iuPerson                       Person.Person

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader

iduSalesOrderDetail            Sales.SalesOrderDetail

uSalesOrderHeader              Sales.SalesOrderHeader

dVendor                        Purchasing.Vendor

iWorkOrder                     Production.WorkOrder

uWorkOrder                     Production.WorkOrder

 

本身的表和视图有稍许个触发器?

自己想精通各样表有多少个触发器,并且什么状态下接触它们。下边我们列出了装有触发器的表以及各样事件的触发器数量。每个表或者视图对于触发器行为都有一个INSTEAD
OF 触发器,可能是UPDATE, DELETE, 或者 INSERT

。不过一个表可以有多个AFTER触发器行为。这多少个将显得在下面的查询中(排除视图):

SELECT

convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] 

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update'

FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers

      WHERE objectpropertyex(parent_ID, N'IsTable') =1

         GROUP BY parent_ID

          )TablesOnly;

--查询结果如下:

Table                            triggers    Delete Insert Update

-------------------------------- ----------- ------ ------ ------

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

倘使超过一个触发器被触发在一个表上,它们不保险顺序,当然也足以使用sp_settriggerorder来决定顺序。通过利用objectpropertyex()元数据函数,需要按照事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 或者
‘ExecIsLastUpdateTrigger’来确认什么人是终极一个执行的触发器
。为了博取第一个触发器,酌情采纳ObjectPropertyEx()
元数据函数,需要输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 或者 ‘ExecIsFirstUpdateTrigger’。

为此大家现在明白了表有什么样触发器,哪些事件触发这多少个触发器。可以使用objectpropertyex()元数据函数,这多少个函数重临很多不比音信,遵照指定的参数不同。通过翻看MSDN中的文档,查看里面的一个文档是否有助于元数据查询,总是值得检查的。

触发器什么时候触发事件?

让我们看一下这个触发器,DML触发器能够在拥有其他时间发出后触发,可是足以在封锁被处理前还要触发INSTEAD
OF触发动作。下边我们就来看看所有的接触的究竟是AFTER 仍然INSTEAD OF
触发器,有事什么日子接触了触发器。

/* 列出触发器,无论它们是否启用,以及触发器事件。*/

SELECT

  convert(CHAR(25),name) AS triggerName,

  convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS TheParent,

       is_disabled,

       CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END

       +Stuff (--get a list of events for each trigger

        (SELECT ', '+type_desc FROM sys.trigger_events te

           WHERE te.object_ID=sys.triggers.object_ID

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events

 FROM sys.triggers;

结果如下:

triggerName               TheParent                        is_disabled events

------------------------- -------------------------------- ----------- ---------

ddlDatabaseTriggerLog     Database (AdventureWorks2012)    1           AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_XML_INDEX, ALTER_FULLTEXT_INDEX, CREATE_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX, CREATE_SPATIAL_INDEX, CREATE_STATISTICS, UPDATE_STAT

t_AB                      dbo.AB                           0           INSTEAD OF INSERT

dEmployee                 HumanResources.Employee          0           INSTEAD OF DELETE

iuPerson                  Person.Person                    0           AFTER INSERT, UPDATE

iPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER INSERT

uPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER UPDATE

uPurchaseOrderHeader      Purchasing.PurchaseOrderHeader   0           AFTER UPDATE

iduSalesOrderDetail       Sales.SalesOrderDetail           0           AFTER INSERT, UPDATE, DELETE

uSalesOrderHeader         Sales.SalesOrderHeader           0           AFTER UPDATE

dVendor                   Purchasing.Vendor                0           INSTEAD OF DELETE

iWorkOrder                Production.WorkOrder             0           AFTER INSERT

uWorkOrder                Production.WorkOrder             0           AFTER UPDATE

 

As you will notice, we used a FOR XML PATH(‘’)
trick

here to make a list of the events for each trigger to make it easier to
read. These events were pulled from the sys.trigger_events view using
a correlated subquery.

留神到大家选用了FOR XML
PATH(‘’)
来列出事件的每一个触发器,更便于读取精晓。sys.trigger_events应用相关子查询来询问那个事件。

触发器的多少长度?

广大数据库人士不赞同冗长触发器的定义,但他俩或许会发现,遵照定义的长短排序的触发器列表是研讨数据库的一种有用艺术。

SELECT convert(CHAR(32),coalesce(object_schema_name(t.object_ID)+'.','')

    +name) AS TheTrigger,

       convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS theParent,

       len(definition) AS length --the length of the definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

ORDER BY length DESC;

访问sys.SQL_modules视图可以查看触发器定义的SQL
DDL,并按大小顺连串出它们,最下边是最大的。

结果:

TheTrigger                       theParent                        length

-------------------------------- -------------------------------- --------

Sales.iduSalesOrderDetail        Sales.SalesOrderDetail           3666

Sales.uSalesOrderHeader          Sales.SalesOrderHeader           2907

Purchasing.uPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   2657

Purchasing.iPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   1967

Person.iuPerson                  Person.Person                    1498

ddlDatabaseTriggerLog            Database (AdventureWorks2012)    1235

Purchasing.dVendor               Purchasing.Vendor                1103

Production.uWorkOrder            Production.WorkOrder             1103

Purchasing.uPurchaseOrderHeader  Purchasing.PurchaseOrderHeader   1085

Production.iWorkOrder            Production.WorkOrder             1011

HumanResources.dEmployee         HumanResources.Employee          604

 

可以吗,我恐怕太挑剔了,不太喜欢太长的,然则逻辑有时候会很长。事实上,前三名在我看来是不可靠的,尽管自己一连倾向于尽可能少地采纳触发器。

这多少个触发器访问了有些对象

在代码中,每个触发器要拜访多少对象(比如表和函数)?

俺们只需要检讨表达式看重项。那多少个查询利用一个视图来列出“软”倚重项(如触发器、视图和函数)。

SELECT coalesce(object_schema_name(parent_id)

          +'.','')+convert(CHAR(32),name) AS TheTrigger,

          count(*) AS Dependencies

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

GROUP BY name, parent_id

ORDER BY count(*) DESC;
--结果:

TheTrigger                               Dependencies

---------------------------------------- ------------

Sales.iduSalesOrderDetail                7

Sales.uSalesOrderHeader                  7

Purchasing.iPurchaseOrderDetail          5

Purchasing.uPurchaseOrderDetail          5

Purchasing.uPurchaseOrderHeader          3

Production.iWorkOrder                    3

Production.uWorkOrder                    3

dbo.t_AB                                 2

Purchasing.dVendor                       2

Person.iuPerson                          2

ddlDatabaseTriggerLog                    1

 

甚至有六个触发器有7个依靠!让大家就Sales.iduSalesOrderDetail来其实看一下,有什么依赖。

特定触发器访问依旧写入哪些对象?

我们可以列出触发器在代码中援引的保有目标

SELECT

  convert(char(32),name) as TheTrigger,

  convert(char(32),coalesce([referenced_server_name]+'.','')

            +coalesce([referenced_database_name]+'.','')

       +coalesce([referenced_schema_name]+'.','')+[referenced_entity_name])
     as referencedObject

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

WHERE name LIKE 'iduSalesOrderDetail';

--查询结果:

TheTrigger                       referencedObject

-------------------------------- --------------------------------

iduSalesOrderDetail              Sales.Customer                 

iduSalesOrderDetail              Person.Person                  

iduSalesOrderDetail              Sales.SalesOrderDetail         

iduSalesOrderDetail              Sales.SalesOrderHeader          

iduSalesOrderDetail              Production.TransactionHistory  

iduSalesOrderDetail              dbo.uspLogError                

iduSalesOrderDetail              dbo.uspPrintError

 

触发器里有什么代码?

现行让我们通过检查触发器的源代码来认同那或多或少。.

SELECT OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') ); 

咱俩事先的查询是科学的,扫描源码可知所有的依赖性项。大量依赖项表名对于数据库的重构等需要分外小心,例如,修改一个基础表的列。

据需要做什么,您可能希望检查来自元数据视图的定义,而不是运用OBJECT_DEFINITION函数。

 SELECT definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');

追寻触发器的代码

There are always plenty of ways of using the metadata views and
functions. I wonder if all these triggers are executing that
uspPrintError procedure?

有成百上千采取元数据视图和函数的法子。想领悟是否享有这一个触发器都推行uspPrintError存储过程?

/* 在有着触发器中找寻字符串 */

 

SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +name) AS TheTrigger, '...'+substring(definition, hit-20,120) +'...'

FROM

  (SELECT name, definition, t.object_ID, charindex('EXECUTE [dbo].[uspPrintError]',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.triggers t

       ON t.object_ID=m.object_ID)f

WHERE hit>0; 

 

结果如图:

图片 1

 

8个引用正在进行这多少个过程。大家在sys.SQL_modules中查找了富有的概念可以找到一个特定的字符串,这种艺术很慢很暴力,可是它是实惠的!

在装有目的中找寻字符串

自身想清楚除了触发器之外是否还有此外对象调用这么些进程?我们略微修改查询以搜寻sys.objects视图,而不是sys.triggers,以搜寻所有具有与之提到的代码的靶子。我们还亟需体现对象的品种

/* 在装有目的中寻找字符串 */

 SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +object_name(object_ID)) AS TheObject, type_desc, '...'+substring(definition,hit-20,120)+'...' as TheExtract

FROM

  (SELECT  type_desc, definition, o.object_ID, charindex('uspPrintError',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.objects o

       ON o.object_ID=m.object_ID)f

WHERE hit>0; 

询问结果如下图:

图片 2

 From this output we can see that, other than the procedure itself where
it is defined, and the triggers, only dbo.uspLogError is executing the
uspPrintError procedure. (see the first column, second line down)

从这一个输出中我们可以见见,除了在概念它的过程本身之外,还有触发器,唯有dbo.uspLogError正在举办uspPrintError过程。(见第一列,第二行往下)

列出劳动器级触发器及其定义

大家可以通过系统视图领悟它们啊?嗯,是的。以下是列出服务器触发器及其定义的说话

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

留神,只美观看有权力看的触发器

总结

  本文商讨过触发器,并且你能查出触发器,以及地下的题目。那里并从未对准有关触发器的询问提供一个完善的工具箱,因为自己只是使用触发器作为示范来显示在询问系统视图时可能利用的部分技术。在大家上学了目录、列和参数之后,大家将赶回触发器,并领会了编写访问系统视图和information
schema视图的询问的局部常见用途。表是元数据的众多方面的底蕴。它们是三种档次的目的的父类,其他元数据如索引是表的性能。大家正在渐渐地努力去发现拥有有关表的信息。期待下期

相关文章