商量SQL Server元数据(二)

来源:http://www.mnuet.com 作者:产品分类 人气:79 发布时间:2019-10-11
摘要:背景 上一篇中,小编介绍了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’来搜索你想要的视图名称。.

在二〇一一会同未来版本,能够行使三个新的表值函数相当大地简化上述查询,并可以幸免各类连接。在底下的询问中,大家将追寻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 中的对象包涵以下:聚合的CL奥迪Q7函数,check 约束,SQL标量函数,CLENCORE标量函数,CL景逸SUV表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CL路虎极光存款和储蓄进度,布置指南,主键约束,老式法则,复制过滤程序,系统基础表,同义词,类别对象,服务队列,CL奔驰M级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

。可是八个表能够有五个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)

只要赶上贰个触发器被触发在一个表上,它们不有限补助顺序,当然也能够动用sp_settriggerorder来调整顺序。通过应用objectpropertyex()元数据函数,需求依照事件输入参数‘ExecIsLastDeleteTrigger’, ‘ExecIsLastInsertTrigger’ 或然‘ExecIsLastUpdateTrigger’来确认何人是终极贰个实践的触发器 。为了博取第三个触发器,酌情选择ObjectPropertyEx() 元数据函数,须求输入参数 ‘ExecIsFirstDeleteTrigger’, ‘ExecIsFirstInsertTrigger’ 恐怕 ‘ExecIsFirstUpdateTrigger’。

故此大家今天知晓了表有什么样触发器,哪些事件触发那一个触发器。能够应用objectpropertyex()元数据函数,那些函数再次回到比非常多见仁见智音信,依照钦命的参数分歧。通过查阅MSDN中的文书档案,查看里面包车型大巴二个文书档案是或不是有助于元数据查询,总是值得检查的。

触发器什么日期触发事件?

让大家看一下这一个触发器,DML触发器能够在有着其余时间发生后触发,不过能够在约束被管理前还要触发INSTEAD OF触发动作。下边大家就来探视全部的接触的毕竟是AFTECRUISER 依然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视图的询问的局地数见不鲜用途。表是元数据的广大地点的功底。它们是两种档案的次序的对象的父类,其余元数据如索引是表的性质。大家正在逐年地拼命去开采全数有关表的音信。期望上期

本文由广东十一选五一定牛发布于产品分类,转载请注明出处:商量SQL Server元数据(二)

关键词:

上一篇:SQLServer之修改CHECK约束

下一篇:没有了

最火资讯