澳门在线威尼斯官方 > 电脑数据库 > 探索SQL Server元数据(一)

原标题:探索SQL Server元数据(一)

浏览次数:111 时间:2019-09-15

简介

  在数据库中,我们除了存款和储蓄数据外,还蕴藏了大气的元数据。它们主要的遵守即是描述数据库怎么建构、配置、以及各个对象的质量等。本篇简要介绍怎么着使用和查询元数据,怎样更管用的治本SQLServer 数据库。

  对部分有经验的数据库开垦和管理人士来说,元数据是极度有价值的。上边小编会介绍一下简易的规律,然后尽量用代码的方式直接证实,终归“talk is cheap show me the code ”。

背景

  上一篇中,作者介绍了SQL Server 允许访问数据库的元数据,为何有元数据,怎样利用元数据。这一篇中作者会介绍如何越发找到各样有价值的音讯。以触发器为例,因为它们往往一起比非常多难题。

 

怎么是动态在线目录?

  每一个关系型数据库系统,举例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.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’来搜索你想要的视图名称。.

在2013及其现在版本,能够使用三个新的表值函数相当的大地简化上述查询,并得以免止各样连接。在底下的查询中,我们将搜索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;

什么样收获上述音信?

因为大家无法平素访谈,需求动用视图和函数来看这么些新闻。只可以看看您权力内的数目。有更加好的方法在顾客数据库中动用数据定义语言(DDL),这一个DDL语句包括CREATE, DROP, ALTE酷路泽, GRANT, DENY, REVOKE 和sp_rename statements 等。总有一种方法能够行使DDL来修改视图中的任何音信,尽管并不再而三分明的。

关系型数据库使用动态的系统视图中的数据描述数据库,可是当前还恐怕有未有规范化。不过有三个分包在各样数据库内的框架结构能够读取这几个音信:就是Information Schema

不走运的是,这几个架构不足以提供丰硕音信,那象征大家要求运用SQL Server 系统数据库的视图和函数来补充消息。接下来须要说多美滋(Dumex)(Dumex)些术语和手艺,笔者会尽恐怕少的内部原因足以让大家轻便地领会这一个示例

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

 威尼斯澳门在线 2

 

* *

唯独当然一个触发器是率先是三个对象,因而一定在sys.objects?

  在我们运用sys.triggers的音信在此以前,须求来重新贰次,全数的数据库对象都设有于sys.objects中,在SQL Server 中的对象包涵以下:聚合的CLSportage函数,check 约束,SQL标量函数,CL帕杰罗标量函数,CLENVISION表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CLSportage存储进度,陈设指南,主键约束,老式法则,复制过滤程序,系统基础表,同义词,体系对象,服务队列,CL景逸SUVDML 触发器,SQL表值函数,表类型,顾客自定义表,独一约束,视图和扩张存款和储蓄进度等。

  触发器是目的所以基础消息一定保存在sys.objects。不走运的是,一时大家需求额外的新闻,这一个新闻可以透过目录视图查询。那个额外数占有是哪些啊?

 

  修改大家运用过的查询,来查询sys.triggers的列,这一次我们会看出额外音信。那些额外列是出自于sys.objects。

 SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

以上这么些让大家掌握在sys.triggers的附加音讯,可是因为它始终是表的子对象,所以有些不相干消息是不会显得在那几个钦定的视图也许sys.triggers中的。今后将要带大家去承接找找那一个新闻。

系统视图

触发器的难点

  触发器是可行的,不过因为它们在SSMS对象能源管理器窗格中不是可知的,所以一般用来提示错误。触发器一时候会略带微妙的地方让其出标题,比如,当导入进程中禁止使用了触发器,而且鉴于有个别原因他们不曾重启。

下边是三个关于触发器的简短提示:

  触发器能够在视图,表只怕服务器上,任何这个目的上都得以有超过1个触发器。普通的DML触发器能被定义来进行代表一些数额修改(Insert,Update只怕Delete)可能在数额修改以往实践。每叁个触发器与只与二个目标处理。DDL触发器与数据库关联只怕被定义在服务器等第,那类触发器一般在Create,Alter或然Drop那类SQL语句试行后触发。

  像DML触发器同样,能够有多少个DDL触发器被创设在同二个T-SQL语句上。三个DDL触发器和讲话触发它的言辞在同贰个事情中运营,所以除了Alter DATABASE之外都足以被回滚。DDL触发器运维在T-SQL语句试行实现后,也正是无法同日而语Instead OF触发器使用。

  三种触发器都与事件有关,在DML触发器中,包涵INSERT, UPDATE, 和DELETE,但是无数风云都足以与DDL触发器关联,稍后大家将明白。

Information Schema

这么些架构是一套视图,视图中是当下数据库的音信。每三个数据库中都有那么些架构,只好看看日前数据库的靶子音信。能够向来访问这个架构的数量在入眼的关系型数据中。个中架构视图不带有数据库安顿音信。

对此分化的关系型数据库之间的拍卖专门的工作这些架构尤其首要。它们非常适合常常专业,举例在做客钱检查是还是不是存在,可是假设急需详细报告则会遭到限制。他们还使用一种稍有分歧的典型命名法:比如,数据库被誉为目录,用户定义的数据类型被喻为“domain”。

在此之前看到MSDN上有人警告说不用选拔INFORMATION_SCHEMA视图来认同对象架构,作者精通是因为SQL Server允许在分化的架构中有一样的表名字,因而当唯有表名称的时候会有模糊。所以本身感觉就算放心使用就好了。

 

在数据库中列出触发器

那正是说怎么获取触发器列表?下边笔者在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

 

包容性视图

包容性视图是保证元数据的视图,在SQL Server 二零零七从前是有系统表帮衬的,何况只向后卓越。只在二〇〇五今后的版本协助对于一些系统表的询问,比如分区表等,唯有部分元数据恐怕性子是对客户可知的。对于包罗相当多客商、群组、剧中人物要么2000本子数据类型的数据库来讲,使用包容性视图是有秘密风险的,因为视图中有个别列存储了客户的ID恐怕项目ID,大概会回去NULL大概触发溢出。

本人的表和视图有几个触发器?

自己想领会种种表有多少个触发器,而且什么情状下接触它们。上面大家列出了全体触发器的表以及各样事件的触发器数量。各种表恐怕视图对于触发器行为都有一个INSTEAD OF 触发器,或然是UPDATE, DELETE, 或许 INSERT

。可是二个表可以有多少个AFTE凯雷德触发器行为。那几个将展现在底下的询问中(排除视图):

SELECT

convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] 

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update'

FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers

      WHERE objectpropertyex(parent_ID, N'IsTable') =1

         GROUP BY parent_ID

          )TablesOnly;

--查询结果如下:

Table                            triggers    Delete Insert Update

-------------------------------- ----------- ------ ------ ------

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

固然超过一个触发器被触发在贰个表上,它们不有限支撑顺序,当然也足以动用sp_settriggerorder来决定顺序。通过应用objectpropertyex()元数据函数,需求基于事件输入参数‘ExecIsLastDeleteTrigger’, ‘ExecIsLastInsertTrigger’ 只怕‘ExecIsLastUpdateTrigger’来确认什么人是最后二个执行的触发器 。为了获得第4个触发器,酌情选取ObjectPropertyEx() 元数据函数,须要输入参数 ‘ExecIsFirstDeleteTrigger’, ‘ExecIsFirstInsertTrigger’ 可能 ‘ExecIsFirstUpdateTrigger’。

由此大家前些天知晓了表有何触发器,哪些事件触发这个触发器。能够应用objectpropertyex()元数据函数,那一个函数再次回到比较多区别音讯,依据钦命的参数差异。通过翻看MSDN中的文书档案,查看里面包车型大巴三个文书档案是不是有利于元数据查询,总是值得检查的。

目录视图

目录视图提供了关于数据库架构的音讯。它们也被数据库引擎本身作者使用,特别在查询优化环节。因而那些视图须求更加高速的艺术来博取元数据。除了复制、备份、数据库维护安排或SQL Server代理目录数据之外,全体元数据都通过那些编目视图公开。

这么些视图用一种极度奇特的秘诀排列,SQL Server对象的共有消息都封存在sys.objects里面。有非常多派生视图,比如外键、约束、服务队列、表、视图和进程,那么些视图用特定于被编目的目的类型的音信来补充一般的靶子音讯

决不SQL Server元数据中的全体剧情都以指标。举个例子,三个列、索引或布满计算音信不是目的。一些如主键约束或增添属性有多个出人意料的两面性,因为它们被被当做为贰个对象,当被胁持键索引的实例化时,它就不是一个指标。有个别对象(首就算封锁)与另一类别型的对象具备父/子关系;父即表。

触发器何时触发事件?

让大家看一下那么些触发器,DML触发器可以在享有其余时间产生后触发,可是足以在封锁被管理前同期触发INSTEAD OF触发动作。下边大家就来探视全部的触发的到底是AFTE奥迪Q7 照旧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利用相关子查询来查询那个事件。

数据层应用程序视图

数据层应用程序视图被用于访谈注册服务器新闻。特殊版本的服务器和音信用来检查这几个本子是还是不是漂移。那是一种作为轻易的自己研讨当前注册数据库版本的方法,直接用T-SQL查询。

触发器的多少长度?

成都百货上千数据库职员不赞同冗长触发器的定义,但他俩或然会发觉,依据定义的尺寸排序的触发器列表是钻探数据库的一种有用艺术。

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

 

可以吗,作者可能太指责了,不太喜欢太长的,不过逻辑有的时候候会十分短。事实上,前三名在笔者眼里是不可相信的,纵然我接连侧向于尽大概少地采用触发器。

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:探索SQL Server元数据(一)

关键词:

上一篇:sql语句语法大全,杰出SQL语句大全

下一篇:没有了