菜单

深究SQL Server元数据(一)

2019年8月2日 - MySQL

背景

  上一篇中,小编介绍了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’来探索你想要的视图名称。.

在二零一二会同未来版本,能够选用二个新的表值函数相当大地简化上述查询,并能够制止种种连接。在底下的询问中,大家将追寻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 贰零零伍起初,只有视图能够查询了,不可能平昔看看数据表了。

 图片 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 中的对象包涵以下:聚合的CLHaval函数,check
约束,SQL标量函数,CL奥迪Q3标量函数,CLEnclave表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CLGL450存款和储蓄进度,布置指南,主键约束,老式准绳,复制过滤程序,系统基础表,同义词,类别对象,服务队列,CLHighlanderDML
触发器,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, ALTE奥迪Q7, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种办法可以利用DDL来修改视图中的任何音讯,纵然并不总是确定的。

关系型数据库使用动态的种类视图中的数据描述数据库,可是近来还应该有未有规范。可是有叁个蕴涵在各样数据库内的架构能够读取那些音讯:正是Information
Schema

不走运的是,这一个架构不足以提供充足音讯,这象征大家供给动用SQL Server
系统数据库的视图和函数来补偿消息。接下来要求说爱他美(Aptamil)些术语和技艺,笔者会尽或许少的细节足以让咱们轻巧地领悟这么些示例

如图所示,如何访问元数据,及其接口

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

。然而叁个表能够有多个AFTE中华V触发器行为。那几个将浮未来上边包车型大巴查询中(排除视图):

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
2007事先是有系统表援救的,而且只向后万分。只在二〇〇六事后的本子协助对于某个系统表的询问,譬喻分区表等,独有部分元数据也许个性是对用户可知的。对于富含相当多用户、群组、剧中人物依然三千版本数据类型的数据库来讲,使用包容性视图是有神秘风险的,因为视图中有的列存款和储蓄了用户的ID或许项目ID,或许会回到NULL也许触发溢出。

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

让我们看一下这一个触发器,DML触发器能够在装有别的时间发出后触发,不过足以在封锁被拍卖前并且触发INSTEAD
OF触发动作。上边大家就来看看全部的触及的到底是AFTE大切诺基 依然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脚本用于支付和保管数据库是一定飞速的。

元数据视图和函数允许试行差相当少不恐怕执行的操作,比方查找信赖于钦点的CL路虎极光用户定义类型或外号类型的参数。

寻觅触发器的代码

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

相比平价的查询实例

上边笔者会议及展览示的例子都已经在2010和二零一三五个版本中测量检验。当然只用到了个别版本的末梢叁个版本更新后的数据库。

下图中显得了全部继续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地图