探寻SQL Server元数据(2)

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

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

小编们只供给检查表明式注重项。这几个查询利用贰个视图来列出“软”依赖项(如触发器、视图和函数)。

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

 

仍然有多少个触发器有多少个依附!让我们就Sales.iduSalesOrderDetail来其实看一下,有何样注重。

自个儿的表和视图有微微个触发器?

自个儿想精晓各样表有多少个触发器,并且什么情况下接触它们。上边大家列出了具有触发器的表以及各类事件的触发器数量。各类表或然视图对于触发器行为都有多少个INSTEAD
OF 触发器,也许是UPDATE, DELETE, 或许 INSERT

。不过三个表能够有三个AFTE途胜触发器行为。这一个将浮今后底下的询问中(排除视图):

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)

只要抢先1个触发器被触发在三个表上,它们不保险顺序,当然也得以应用sp_settriggerorder来决定顺序。通过采纳objectpropertyex()元数据函数,须要基于事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 或然‘ExecIsLastUpdateTrigger’来确认何人是最后三个推行的触发器
。为了拿走第2个触发器,酌情采取ObjectPropertyEx()
元数据函数,须要输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 或许 ‘ExecIsFirstUpdateTrigger’。

故而大家以后知道了表有哪些触发器,哪些事件触发这几个触发器。能够运用objectpropertyex()元数据函数,那么些函数重回许多见仁见智音讯,依照内定的参数差别。通过翻看MSDN中的文书档案,查看里面的三个文书档案是还是不是有助于元数据查询,总是值得检查的。

那就是说如何找到触发器的多寡?

*  以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

 

就此大家多这一个音信有了越来越好的驾驭,有了2个索引的目录。那些概念有一点点令人头晕,但是另壹方面,它也是一定简单的。大家能够意识到元数据,再找个查询中,须要做的正是退换这几个单词‘triggers’来搜索你想要的视图名称。.

在二〇一二会同现在版本,能够使用二个新的表值函数十分大地简化上述查询,并可避防止种种连接。在上面的询问中,我们将追寻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;

检索触发器的代码

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

 

7个引用正在实践这一个历程。大家在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 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');

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

咱俩得以列出触发器在代码中引用的享有目的

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

 

在数据库中列出触发器

那么怎么获取触发器列表?上面作者在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视图中,然则你会挂1漏万在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

 

列出服务器级触发器及其定义

我们能够经过系统视图通晓它们啊?嗯,是的。以下是列出服务器触发器及其定义的言辞

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

留神,只好见到有权力看的触发器

触发器曾几何时触发事件?

让我们看一下那么些触发器,DML触发器能够在全体其余时间发出后触发,不过足以在封锁被拍卖前而且触发INSTEAD
OF触发动作。上边大家就来看看全数的触及的到底是AFTELX570 如故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利用相关子查询来询问这么些事件。

总结

  本文研究过触发器,并且你能识破触发器,以及潜在的主题材料。这里并不曾针对有关触发器的查询提供2个宏观的工具箱,因为本人只是使用触发器作为示范来展现在询问系统视图时或许应用的部分本领。在大家学习了目录、列和参数之后,大家将回来触发器,并掌握了编写访问系统视图和information
schema视图的询问的局地常见用途。表是元数据的繁多方面包车型客车根基。它们是三种等级次序的指标的父类,别的元数据如索引是表的属性。我们正在日渐地努力去开掘装有有关表的信息。期待上期

触发器的多少长度?

重重数据库人士不接济冗长触发器的定义,但他们恐怕会发觉,依照定义的尺寸排序的触发器列表是钻探数据库的①种有用艺术。

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

 

好吧,笔者说不定太指责了,不太喜欢太长的,可是逻辑一时候会非常长。事实上,前3名在笔者眼里是不可信赖的,即使作者接连倾向于尽大概少地动用触发器。

背景

  上一篇中,作者介绍了SQL Server
允许访问数据库的元数据,为何有元数据,怎样行使元数据。那1篇中作者会介绍怎么样尤其找到各类有价值的音信。以触发器为例,因为它们往往一同许多主题素材。

 

唯独当然1个触发器是率先是3个对象,因而一定在sys.objects?

  在我们应用sys.triggers的新闻此前,需求来再一次2回,全部的数据库对象都留存于sys.objects中,在SQL
Server 中的对象包括以下:聚合的CLRubicon函数,check
约束,SQL标量函数,CL汉兰达标量函数,CLLAND表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CLPAJERO存储进度,铺排指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,连串对象,服务队列,CLGL450DML
触发器,SQL表值函数,表类型,用户自定义表,唯1约束,视图和扩充存款和储蓄进程等。

  触发器是指标所以基础消息一定保存在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对象能源管理器窗格中不是可知的,所以一般用来唤醒错误。触发器临时候会略微微妙的地方让其出难题,举例,当导入进程中禁止使用了触发器,并且由于某个原因他们尚无重启。

下边是贰个有关触发器的简练提醒:

  触发器能够在视图,表恐怕服务器上,任何那一个指标上都得以有超过3个触发器。普通的DML触发器能被定义来实践替代一些数额修改(Insert,Update或许Delete)大概在多少修改以往推行。每一个触发器与只与二个目的处理。DDL触发器与数据库关联也许被定义在服务器等第,那类触发器一般在Create,Alter或许Drop那类SQL语句施行后触发。

  像DML触发器一样,能够有多个DDL触发器被创建在同2个T-SQL语句上。七个DDL触发器和话语触发它的口舌在同四个思想政治工作中运作,所以除了Alter
DATABASE之外都能够被回滚。DDL触发器运营在T-SQL语句推行完成后,也正是不可能当做Instead
OF触发器使用。

  二种触发器都与事件有关,在DML触发器中,蕴含INSERT, UPDATE,
和DELETE,但是不少轩然大波都能够与DDL触发器关联,稍后大家将理解。

相关文章