探索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视图的询问的一些一般用途。表是元数据的众地方的根底。它们是几种植档次的目标的父类,其他元数据如索引是表明底性。我们在逐年地努力去发现有有关表底音讯。期待下期

相关文章