深究SQL Server元数据(二)

发布时间:2019-01-14  栏目:sqlite  评论:0 Comments

背景

  上一篇中,我介绍了SQL Server
允许访问数据库的元数据,为何有元数据,怎样接纳元数据。这一篇中我会介绍怎么着更加找到各个有价值的音讯。以触发器为例,因为它们往往一起很多问题。

 

简介

  在数据库中,我们除了存储数据外,还蕴藏了大气的元数据。它们重要的效能就是讲述数据库怎么建立、配置、以及各个对象的性能等。本篇简单介绍怎么着使用和查询元数据,怎么着更使得的治本SQLServer
数据库。

  对部分有经验的数据库开发和管理人员而言,元数据是老大有价值的。上边我会介绍一下大概的原理,然后尽量用代码的不二法门直接注脚,毕竟“talk
is cheap show me the code ”。

那么怎么样找到触发器的数目?

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

咋样是动态在线目录?

  每一个关系型数据库系统,比如SQL Server
一定要提供关于它的协会的消息,那个新闻往往需要经过sql语法来查询。通常这一个消息被封存在指定数据表的构造中。这代表数据库中有二种不同的表:一是用户自定义的表和系统表或者视图(包含元数据)。从SQL
Server 2005先河,只有视图可以查询了,无法一向看出数据表了。

 图片 1

系统视图

这种系统表或者视图的组成平时参考关系型数据库理论的文献叫做作为系统目录或者数额字典。

在数据库内部,有一部分系统表平昔追踪数据库中生出的每一件事情。系统表存储像表、活动、列、索引等事务。这个完全符合EdgarCodd
的关系型数据库试试的十三条轨道直译。这一个规则就是概念动态在线目录,它就是“关于数据的数目”,也叫作元数据。

 埃德加(Edgar) Codd  准则4, 描述如下:

‘The database description is represented at the logical level in the
same way as ordinary data, so that authorized users can apply the same
relational language to its interrogation as they apply to the regular
data.’

翻译:像一般数据一致,在逻辑层的数目表明了对数据库的讲述,以便于授权用户能利用相同的SQL语言来查询元数据,就好似查询常规数量一致。

在SQL
Server中,能够因而系统视图或者架构视图直接访问动态在线目录,方便用户越来越急迅的开发和管制数据库。

不过当然一个触发器是首先是一个对象,由此一定在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中的。现在即将带我们去继续找找那多少个信息。

哪些得到上述信息?

因为我们不可以直接访问,需要使用视图和函数来看这一个信息。只赏心悦目看你权力内的多少。有更好的办法在用户数据库中行使数据定义语言(DDL),这个DDL语句包括CREATE,
DROP, ALTER, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种艺术能够利用DDL来修改视图中的任何信息,尽管并不连续肯定的。

关系型数据库使用动态的连串视图中的数据描述数据库,不过最近还有没有规则。但是有一个包含在每个数据库内的架构可以读取这多少个音讯:就是Information
Schema

不幸运的是,这些架构不足以提供丰盛音信,这象征我们需要采取SQL Server
系统数据库的视图和函数来填补消息。接下来需要解释一些术语和技术,我会尽可能少的底细足以让咱们轻松地领略这么些示例

如图所示,怎么样访问元数据,及其接口

 图片 2

 

* *

触发器的题材

  触发器是实惠的,不过因为它们在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

 

Information Schema

其一架构是一套视图,视图中是时下数据库的信息。每一个数据库中都有其一架构,只可以看到眼前数据库的目的信息。可以间接访问这一个架构的数目在紧要的关系型数据中。其中架构视图不含有数据库部署新闻。

对于不同的关系型数据库之间的处理工作这一个架构尤其重点。它们相当适合平时工作,例如在访问钱检查是否留存,然则假如急需详细告诉则会碰着限制。他们还利用一种稍有两样的标准命名法:例如,数据库被喻为目录,用户定义的数据类型被喻为“domain”。

事先看到MSDN上有人告诫说不要选取INFORMATION_SCHEMA视图来确认对象架构,我知道是因为SQL
Server允许在不同的架构中有一样的表名字,因而当唯有表名称的时候会有模糊。所以我以为虽然放心使用就好了。

 

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

我想理解各种表有几个触发器,并且什么情况下接触它们。下面我们列出了颇具触发器的表以及各类事件的触发器数量。每个表或者视图对于触发器行为都有一个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中的文档,查看里面的一个文档是否有助于元数据查询,总是值得检查的。

兼容性视图

兼容性视图是爱护元数据的视图,在SQL Server
2005事先是有系统表扶助的,并且只向后非凡。只在2005随后的版本辅助对于某些系统表的询问,例如分区表等,只有部分元数据或者特性是对用户可见的。对于富含很多用户、群组、角色要么2000版本数据类型的数据库而言,使用兼容性视图是有神秘风险的,因为视图中部分列存储了用户的ID或者项目ID,可能会回来NULL或者触发溢出。

触发器何时触发事件?

让大家看一下这多少个触发器,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行使相关子查询来查询这多少个事件。

目录视图

目录视图提供了有关数据库架构的音讯。它们也被数据库引擎自己本身使用,尤其在询问优化环节。因而这么些视图需要更高效的情势来收获元数据。除了复制、备份、数据库维护计划或SQL
Server代理目录数据之外,所有元数据都经过这些编目视图公开。

这些视图用一种非凡独特的措施排列,SQL
Server对象的共有音信都封存在sys.objects里面。有为数不少派生视图,比如外键、约束、服务队列、表、视图和过程,那一个视图用特定于被编目的目标类型的音讯来补偿一般的靶子音讯

不要SQL
Server元数据中的所有情节都是目的。例如,一个列、索引或分布总括消息不是目的。一些如主键约束或扩展属性有一个意料之外的两面性,因为它们被被看做为一个目标,当被威迫键索引的实例化时,它就不是一个对象。有些对象(主如果封锁)与另一序列型的对象拥有父/子关系;父即表。

触发器的多长?

广大数据库人士不赞同冗长触发器的定义,但他俩也许会发觉,依照定义的尺寸排序的触发器列表是琢磨数据库的一种有用艺术。

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

 

好啊,我可能太挑剔了,不太喜欢太长的,但是逻辑有时候会很长。事实上,前三名在我看来是不可靠的,即使我一连倾向于尽可能少地利用触发器。

数据层应用程序视图

数据层应用程序视图被用于访问注册服务器音信。特殊版本的服务器和信息用来检查这么些本子是否漂移。那是一种作为容易的检查当前登记数据库版本的措施,直接用T-SQL查询。

这么些触发器访问了不怎么对象

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

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

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来实在看一下,有什么样倚重。

动态管理视图和效能(DMVs)

DMV一般用来调优,诊断问题和监察数据库服务器状态。最重要的效能就是提供了一种办法来查询数据库的利用音信。例如,不仅查询到目录,而且可以查询到使用量的排序和耗时等。

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

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

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

 

元数据function

还有为数不少元数据函数,如object_name()或col_name(),它们提供有关当前数据库中的情势效用域对象的新闻。通过制止在元数据表明式中开展显式连接,它们提供了获取新闻的捷径,因而,当与编目视图一起使用时,它们得以协助您更快地得到有关元数据的音讯。

触发器里有哪些代码?

明天让我们通过检查触发器的源代码来确认这或多或少。.

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');

目录存储过程

有许多囤积过程的基本点功效是为SQL
Server的ODBC驱动程序提供元数据音信。当您建立ODBC连接时,该信息作为数据对象的聚众。然则,这么些信息经常是可用的,并且可以像其余其余存储过程一样从SQL中选取。它们通常被认为不如目录视图有用,因为存储过程再次来到的结果必须利用INSERT插入一个表或者表变量中,需要运用INSERT
… EXECUTE 语法。

缘何元数据视图和机能很重要?

元数据视图和函数允许你寻找元数据,提供对数据库报告和总结,找出何人有权力查看或改动什么数据,让您减掉重复输入,让几乎拥有隐藏在SQL
Server Management
Studio的音信可查询,使部署脚本更安全,更可靠,找出多年来的更动或创立,神速处理局部函数或过程,确定已登记数据库的版本,审计用于编码实践的数据库代码,发现重复索引并且同意让利扣低效的点击操作。当与另外SQL
Server工具(如默认跟踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于支付和管理数据库是一定快捷的。

元数据视图和函数允许实施几乎不能够实施的操作,例如查找看重于指定的CLR用户定义类型或别名类型的参数。

查找触发器的代码

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; 

 

结果如图:

图片 3

 

8个引用正在实践这个过程。我们在sys.SQL_modules中找找了具有的定义可以找到一个一定的字符串,这种格局很慢很暴力,可是它是卓有效率的!

自己是咋样渐渐使用的?

上学使用元数据视图和函数的率先品级是收集从各类名牌的数据源(如SQL Server
Central)中应用它们的询问。可以在MSDN上询问到。使用记录工具保存这些查询。假若它是一个用以保存注释或部分的工具,能够让你在其他地点轻松地拿到查询,那么它将会有所协助。一段时间后,就足以依照使用需要对那么些查询稍作修改。然后,不需要在object
browser窗格中检索表列表,您很快就足以从集合中获取适当的查询,执行它,并飞快获取消息。

在拥有目的中搜索字符串

本身想知道除了触发器之外是否还有其余对象调用那些进程?大家多少修改查询以寻找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; 

查询结果如下图:

图片 4

 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过程。(见第一列,第二行往下)

正如实用的询问实例

下边我会呈现的例子都早已在2008和2012
四个本子中测试。当然只用到了独家版本的末尾一个版本更新后的数据库。

下图中呈现了富有继续sys.objects列的视图。这意味它们除了具有这个列以外,还有列的相应品种。这是视图所有的音信比如create_date也都源于sys.objects

 图片 5

* *

要列出数据库中的所有视图(存储过程和外键),只需实施以下操作 …

 

SELECT  object_schema_name(object_id)+'.'+name FROM sys.views;

 SELECT  object_schema_name(object_id)+'.'+name FROM sys.procedures;

SELECT name AS Foreign_key,object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent,

object_schema_name(referenced_object_ID)+’.’+object_name(referenced_object_ID)
AS referenced

FROM sys.foreign_keys;

 

对于拥有其他的,您需要利用一个系列函数来过滤您想要的靶子。下面的代码提供了有些行之有效的以身作则。因为我们只拿到对象的称呼,所以选拔sys.objects,它富有所有数据库对象共有的焦点信息的视图。假如我们需要一定于特定类型对象的音信,比如主键是否拥有系统生成的称谓,那么你就必须为该特定类型的目的使用视图。

/* The Tables */
  --数据库中的所有用户表
    SELECT
      ob.name AS User_Table, Coalesce(ep.value, '') AS documentation
    FROM sys.objects AS ob
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = ob.object_id
           AND ep.class = 1
           AND ep.minor_id = 0
    WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1 

/* The Views */
  --视图
  SELECT ob.name AS ViewName, Coalesce(ep.value, '') AS documentation
  FROM sys.objects ob  LEFT OUTER JOIN sys.extended_properties AS ep
      ON ep.major_id = ob.object_id
         AND ep.class = 1
         AND ep.minor_id = 0
  WHERE objectproperty(ob.object_id,'IsView')= 1 


/* The Check Constraints */
 --Check约束
    SELECT
      objects.name AS Name_of_Check_Constraint,
      Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
   Coalesce(ep.value,'') AS documentation
   FROM sys.objects
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = objects.object_id AND ep.class=1
       AND ep.name='MS_Description'--microsoft 公约
    WHERE ObjectProperty(objects.object_id, 'IsCheckCnst') = 1 

/* The Constraints */ 

SELECT
--约束
  objects.name AS Name_of_Constraint, --see all constraints and parent table
  Lower(Replace(type_desc,'_',' ')),--the type of constraint
  Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' 
WHERE ObjectProperty(objects.object_id, 'IsConstraint') = 1;

/* The Defaults */
--默认
SELECT
  objects.name, 
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' 
WHERE ObjectProperty(objects.object_id, 'IsDefault') = 1;

/* The Default Constraints */
--数据库及其父表中的所有默认约束
SELECT objects.name AS Name_of_Default_Constraint,--see all Default constraints and parent table
   Coalesce(ep.value,'') AS documentation,
object_schema_name(objects.parent_object_id)+'.'+object_name(objects.parent_object_id) AS parent,
   Coalesce(EP_parent.value,'') AS documentation
FROM sys.objects 
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' --the microsoft convention
  LEFT OUTER JOIN sys.extended_properties AS EP_parent
    ON ep.major_id = objects.parent_object_id
       AND ep.name = 'MS_Description' --the microsoft convention   
 WHERE objectproperty(objects.object_id,'IsDefaultCnst')= 1;

/* The Executables */
--数据库中的所有可执行文件(过程、函数等)
SELECT
  oe.name AS Name_Of_Executable,
  Replace(Lower(oe.type_desc), '_', ' ') AS Type_Of_Executable,
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oe
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oe.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(oe.object_id, 'IsExecuted') = 1;


/* The Extended Stored Procedures */ 
--数据库中的所有扩展存储过程
SELECT
  oep.name AS Name_of_Extended_Procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oep
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oep.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(oep.object_id, 'IsExtendedProc') = 1;

/* The Inline Functions */ 
--数据库中的所有内联函数
SELECT ilf.name AS Inline_function,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ilf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ilf.object_id
       AND EP.name = 'MS_Description'
 WHERE objectproperty(ilf.object_id,'IsInlineFunction')= 1;

/* The Primary Keys */ 
--数据库中的所有主键及其父表
SELECT
  pk.name AS Primary_key,
  Object_Schema_Name(pk.parent_object_id) + '.' + Object_Name(pk.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS KeyDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS pk
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = pk.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = pk.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE ObjectProperty(pk.object_id, 'IsPrimaryKey') = 1;

/* The Stored Procedures */
--数据库中的所有存储过程
SELECT
  sp.name AS Stored_procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sp
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sp.object_id
       AND EP.minor_id = 0
       AND EP.name = 'MS_Description'  
WHERE ObjectProperty(sp.object_id, 'IsProcedure') = 1;

/* The Queues */ 
--数据库中的所有队列
SELECT
  q.name AS QueueName, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS q
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = q.object_id
       AND EP.name = 'MS_Description'   
WHERE ObjectProperty(q.object_id, 'IsQueue') = 1;

/* The Rules */ 
--数据库中的所有旧式规则
SELECT
  ru.name AS RuleName, --old-fashioned sybase-style rule
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ru
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ru.object_id
       AND EP.name = 'MS_Description'  
WHERE ObjectProperty(ru.object_id, 'IsRule') = 1;

/* The Scalar Functions */ 
--数据库中的所有标量函数。
SELECT
  sf.name AS Scalar_function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sf.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(sf.object_id, 'IsScalarFunction') = 1;

/* The System Tables */ 
--据库中的所有系统表
SELECT
  st.name AS System_table, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS st
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = st.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(st.object_id, 'IsSystemTable') = 1;

--数据库中的所有表,包括系统表
SELECT
  at.name AS TableName, 
  Lower(Replace(type_desc,'_',' ')),--约束的类型
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS at
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = at.object_id
       AND EP.name = 'MS_Description'
WHERE ObjectProperty(at.object_id, 'IsTable') = 1;

/* The TVFs*/ 
--数据库中的所有表值函数
SELECT
  tvf.name AS Table_Valued_Function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS tvf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tvf.object_id
       AND EP.name = 'MS_Description' --the microsoft convention 
WHERE ObjectProperty(tvf.object_id, 'IsTableFunction') = 1;

--数据库及其所有触发器。
SELECT
  tr.name AS TriggerName,
  Object_Schema_Name(tr.parent_object_id) + '.' + Object_Name(tr.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS TriggerDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS tr
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tr.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = tr.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE ObjectProperty(tr.object_id, 'IsTrigger') = 1;

/* The Unique Constraints */ 
--数据库及其父表中的所有惟一约束
SELECT uc.name AS Unique_constraint,--所有唯一的约束
object_schema_name(uc.parent_object_id)+'.'+object_name(uc.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS ConstraintDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS uc
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = uc.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = uc.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE objectproperty(uc.object_id,'IsUniqueCnst')= 1;

 

 

本来大家也得以调整这些语句来便于我们的准确查找,比如:

–数据库中的所有视图在过去两周内被修改的有:

SELECT name AS ViewName, convert(char(11),modify_date,113)

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsView')= 1

AND modify_date > dateadd(week,-2, GetDate());

 

–上个月开创的具备目的的名称和档次

SELECT coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name AS ObjectName,

convert(varchar(30),lower(replace(type_desc,'_',' ')))

FROM sys.objects obj

WHERE create_date > dateadd(month,-1, GetDate());

–DBO架构中所有主旨对象的名号和类型

SELECT coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name AS ObjectName,

convert(varchar(30),lower(replace(type_desc,'_',' '))) as ObjectType

FROM sys.objects obj

WHERE parent_object_ID=0

AND schema_ID = schema_ID('dbo'); 

 

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

我们可以通过系统视图了解它们啊?嗯,是的。以下是列出服务器触发器及其定义的言语

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

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

总结

  如上,到这级别简单实用丰盛了。们已经介绍了相似的论战,并介绍了查找数据库中的内容的骨干格局。在下一篇中我将会深刻介绍触发器并且找到有效信息的排序以方便可以经过系统视图从动态在线目录中搜集的有用音讯。

 

总结

  本文研究过触发器,并且你能查出触发器,以及地下的问题。这里并从未指向有关触发器的查询提供一个全面的工具箱,因为自身只是利用触发器作为示范来突显在查询系统视图时可能拔取的局部技艺。在大家上学了目录、列和参数之后,我们将再次回到触发器,并询问了编制访问系统视图和information
schema视图的查询的一对平凡用途。表是元数据的重重上边的根基。它们是几体系型的靶子的父类,其他元数据如索引是表的习性。大家正在逐渐地大力去发现具有关于表的音讯。期待下期

留下评论

网站地图xml地图