探讨SQL Server元数据(二)

发布时间:2018-11-15  栏目:SQL  评论: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

网视图

这种系统表或者视图的重组日常参考关系项目数据库理论的文献称作为系统目录或者数字典。

当数据库中,有一对系统表一直追踪数据库被来的各级一样项事情。系统表存储像表、活动、列、索引等工作。这些完全符合Edgar
Codd
的关联项目数据库试试的十三漫漫轨道直译。这个规则就是是概念动态在线目录,它就是“关于数据的数目”,也于作元数据。

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