Server数据库元数据

简介

  在数据库中,大家除了存储数据外,还蕴藏了多量的元数据。它们紧要的效用就是描述数据库怎么建立、配置、以及种种对象的性质等。本篇简单介绍怎么着利用和查询元数据,如何更实用的田间管理SQLServer
数据库。

  对有的有经历的数据库开发和管理人士而言,元数据是格外有价值的。下边小编会介绍一下简便的法则,然后尽量用代码的法子直接表明,毕竟“talk
is cheap show me the code ”。

背景

  上一篇中,小编介绍了SQL Server
允许访问数据库的元数据,为啥有元数据,如何采纳元数据。这一篇中小编会介绍怎样特别找到各样有价值的消息。以触发器为例,因为它们往往一起很多难点。

 

在SQL
server中,这些函数其实用的挺多的,借使您没用过,想必你早晚在何地看到过,未来迷迷糊糊,似懂非懂,今天小编仔细讲解下它的功效。

from :

什么是动态在线目录?

  每贰个关系型数据库系统,比如SQL Server
一定要提供关于它的社团的消息,那一个音讯往往须求经过sql语法来询问。平常这么些音讯被封存在指定数据表的布局中。那象征数据库中有二种不一样的表:一是用户自定义的表和系统表只怕视图(包蕴元数据)。从SQL
Server 二〇〇六初阶,唯有视图可以查询了,无法一向看看数据表了。

 997755.com澳门葡京 1

系统视图

那种系统表或许视图的组合平日参考关系型数据库理论的文献叫做作为系统目录大概数额字典。

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

 埃德加 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

 

从而大家多这么些音讯有了更好的明亮,有了1个索引的目录。那些概念有点令人头晕,然而另一方面,它也是一对一简单的。大家能够意识到元数据,再找个查询中,须要做的就是改变那些单词‘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;

  我们都清楚在数据库中有壹个种类表sysobjects,里面储存了数据库各种对象的新闻。可以查询下看看结果。可以看来每一种对象都有3个ID,这么些表存储了表,存储进程,触发器,视图等相关音讯。注意:字段没有。

元数据简介

怎么拿到上述消息?

因为大家无法直接访问,必要利用视图和函数来看那么些消息。只可以看看你权力内的数额。有更好的办法在用户数据库中应用数据定义语言(DDL),那么些DDL语句包涵CREATE,
DROP, ALTE卡宴, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种格局可以使用DDL来修改视图中的任何音信,即使并不总是肯定的。

关系型数据库使用动态的体系视图中的数据描述数据库,可是如今还有没有规则。可是有2个分包在各种数据库内的架构可以读取那一个音讯:就是Information
Schema

不走运的是,这么些架构不足以提供丰富消息,那代表咱们须要使用SQL Server
系统数据库的视图和函数来填补新闻。接下来要求说美素佳儿(Friso)(Aptamil)些术语和技艺,作者会尽或然少的细节足以让大家轻松地驾驭这个示例

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

 997755.com澳门葡京 2

 

* *

唯独当然贰个触发器是首先是二个目标,因而一定在sys.objects?

  在我们使用sys.triggers的新闻此前,须求来再一次一回,所有的数据库对象都存在于sys.objects中,在SQL
Server 中的对象包蕴以下:聚合的CLCRUISER函数,check
约束,SQL标量函数,CL酷路泽标量函数,CL途睿欧表值函数,SQL内联表值函数,内部表,SQL存储进程,CLLacrosse存储进程,安顿指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,体系对象,服务队列,CL讴歌ZDXDML
触发器,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中的。未来将要带我们去继承找找这一个音讯。

  object_id就是根据目的名称再次回到该对象的id.
  object_name是基于目标id重返对象名称.
 

元数据 (metadata)
最普遍的定义为“有关数据的布局数据”,只怕再简单一点就是“关于数据的消息”,平时生活中的图例、教室目录卡和名片等都足以用作是元数据。在关系型数据库管理系统
(DBMS) 中,元数据描述了数额的布局和含义。比如在保管、维护 SQL Server
可能是支付数据库应用程序的时候,大家平常要博取一些关乎到数据库架构的音信:

系统视图

触发器的题材

  触发器是有效的,不过因为它们在SSMS对象能源管理器窗格中不是可知的,所以一般用来唤起错误。触发器有时候会有点微妙的地方让其出标题,比如,当导入进度中禁用了触发器,并且鉴于某个原因他们从未重启。

上面是1个有关触发器的总结指示:

  触发器可以在视图,表恐怕服务器上,任何那一个目的上都得以有超越三个触发器。普通的DML触发器能被定义来举行替代一些数量修改(Insert,Update或许Delete)或许在数码修改之后执行。每3个触发器与只与三个对象管理。DDL触发器与数据库关联或许被定义在服务器级别,那类触发器一般在Create,Alter恐怕Drop那类SQL语句执行后触发。

  像DML触发器一样,能够有多个DDL触发器被成立在同3个T-SQL语句上。3个DDL触发器和言语触发它的口舌在同二个作业中运作,所以除了Alter
DATABASE之外都足以被回滚。DDL触发器运行在T-SQL语句执行完成后,也等于无法同日而语Instead
OF触发器使用。

  二种触发器都与事件有关,在DML触发器中,包罗INSEQX56T, UPDATE,
和DELETE,然则无数风云都足以与DDL触发器关联,稍后大家将领会。

  select object_id(对象名)等同于:
  select id from sysobjects where name=对象名

◆有个别数据库中的表和视图的个数以及名称;

Information Schema

本条架构是一套视图,视图中是当下数据库的新闻。每七个数据库中都有那个架构,只可以见到眼下数据库的对象消息。可以间接访问这几个架构的数量在重大的关系型数据中。其中架构视图不分包数据库安顿消息。

对此分裂的关系型数据库之间的处理工作这么些架构尤其紧要。它们卓殊适合日常工作,例如在做客钱检查是否存在,但是假如需求详细报告则会惨遭限制。他们还运用一种稍有两样的标准命名法:例如,数据库被誉为目录,用户定义的数据类型被喻为“domain”。

事先看到MSDN上有人警告说毫无使用INFO奥迪Q3MATION_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

 

  select object_name(id号)等同于:
  select name from sysobjects where id=id号

◆某些表或然视图中列的个数以及每一列的称呼、数据类型、长度、精度、描述等;

兼容性视图

包容性视图是尊崇元数据的视图,在SQL Server
2007以前是有系统表支持的,并且只向后极度。只在二〇〇五随后的版本协理对于某个系统表的询问,例如分区表等,只有部分元数据或然特性是对用户可知的。对于富含很多用户、群组、角色要么三千本子数据类型的数据库而言,使用包容性视图是有地下风险的,因为视图中一些列存储了用户的ID或然项目ID,大概会重回NULL恐怕触发溢出。

997755.com澳门葡京,自个儿的表和视图有微微个触发器?

作者想知道各类表有多少个触发器,并且什么景况下接触它们。上面我们列出了装有触发器的表以及各样事件的触发器数量。逐个表恐怕视图对于触发器行为都有贰个INSTEAD
OF 触发器,大概是UPDATE, DELETE, 或许 INSE酷威T

。不过贰个表可以有多少个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)

借使跨越多少个触发器被触发在3个表上,它们不保证顺序,当然也足以使用sp_settriggerorder来决定顺序。通过选用objectpropertyex()元数据函数,必要依据事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 可能‘ExecIsLastUpdateTrigger’来确认哪个人是最后1个实践的触发器
。为了赢得第二个触发器,酌情选用ObjectPropertyEx()
元数据函数,须要输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 或然 ‘ExecIsFirstUpdateTrigger’。

于是大家将来知道了表有何样触发器,哪些事件触发那几个触发器。可以拔取objectpropertyex()元数据函数,这么些函数重临很多不等音讯,依据指定的参数不相同。通过查阅MSDN中的文档,查看里面的2个文档是还是不是有助于元数据查询,总是值得检查的。

 

◆有个别表上定义的羁绊;

目录视图

目录视图提供了有关数据库架构的新闻。它们也被数据库引擎本人小编使用,越发在询问优化环节。因而那几个视图须要更高效的章程来获取元数据。除了复制、备份、数据库维护布置或SQL
Server代理目录数据之外,所有元数据都通过这几个编目视图公开。

这一个视图用一种杰出独特的主意排列,SQL
Server对象的共有音讯都保留在sys.objects里面。有过多派生视图,比如外键、约束、服务队列、表、视图和进程,这个视图用特定于被编目标靶子类型的新闻来补偿一般的对象消息

绝不SQL
Server元数据中的所有内容都以目的。例如,2个列、索引或分布总括音讯不是目的。一些如主键约束或扩大属性有2个意料之外的两面性,因为它们被被用作为贰个对象,当被威胁键索引的实例化时,它就不是多个目的。有个别对象(紧借使约束)与另一种档次的目标具备父/子关系;父即表。

触发器哪天触发事件?

让大家看一下那么些触发器,DML触发器可以在享有其余时间发生后触发,可是可以在约束被处理前还要触发INSTEAD
OF触发动作。上边大家就来探望所有的接触的到底是AFTETiguan 依然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(‘’)
trickServer数据库元数据。
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 SETucsonVELAND 两千以上版本都匡助这些函数。

◆有个别表上定义的目录以及主键/外键的音信。

数据层应用程序视图

数据层应用程序视图被用来访问注册服务器消息。特殊版本的服务器和音讯用来检查那一个本子是或不是漂移。那是一种作为不难的反省当前登记数据库版本的主意,直接用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

 

可以吗,作者或许太挑剔了,不太喜欢太长的,不过逻辑有时候会十分短。事实上,前三名在作者看来是不可靠的,即便笔者老是倾向于尽大概少地接纳触发器。

除此以外一种申明:

下边大家将介绍二种得到元数据的不二法门。

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

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

那些触发器访问了略微对象

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

咱们只须求检查表明式倚重项。那一个查询利用三个视图来列出“软”正视项(如触发器、视图和函数)。

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

 

居然有七个触发器有几个依靠!让大家就Sales.iduSalesOrderDetail来其实看一下,有哪些依赖。

OBJECT_ID

收获元数据

元数据function

还有许多元数据函数,如object_name()或col_name(),它们提供有关当前数据库中的情势作用域对象的消息。通过防止在元数据表明式中进行显式连接,它们提供了获取新闻的近便的小路,由此,当与编目视图一起行使时,它们可以扶持您更快地赢得有关元数据的新闻。

一定触发器访问照旧写入哪些对象?

咱俩得以列出触发器在代码中引用的享有目的

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

 

回到架构范围内对象的数据库对象标识号。

运用系统存储进度与系统函数访问元数据

目录存储进程

有成千上万储存进度的要害功用是为SQL
Server的ODBC驱动程序提供元数据新闻。当你建立ODBC连接时,该信息作为数据对象的聚集。不过,那一个新闻平日是可用的,并且可以像其余其他存储进度同样从SQL中利用。它们平常被认为不如目录视图有用,因为存储进度重回的结果必须拔取INSELANDT插入二个表大概表变量中,必要利用INSELX570T
… EXECUTE 语法。

缘何元数据视图和功能很重大?

元数据视图和函数允许你寻找元数据,提供对数据库报告和小结,找出哪个人有权力查看或改变什么数据,让你减弱重复输入,让大概所有隐藏在SQL
Server Management
Studio的音信可查询,使布署脚本更安全,更牢靠,找出以来的更改或创办,赶快处理部分函数或进度,确定已注册数据库的本子,审计用于编码实践的数据库代码,发现重复索引并且同意降价扣低效的点击操作。当与其余SQL
Server工具(如默许跟踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于开发和治本数据库是卓殊迅速的。

元数据视图和函数允许实施大概不能实施的操作,例如查找倚重于指定的CL奔驰M级用户定义类型或别名类型的参数。

触发器里有何样代码?

距今让我们通过检查触发器的源代码来确认那或多或少。.

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

997755.com澳门葡京 3重点提示

得到元数据最常用的法子是利用 SQL Server 提供的种类存储进度与系统函数。

自作者是如何逐步使用的?

上学使用元数据视图和函数的率先品级是收集从各个名牌的数据源(如SQL Server
Central)中使用它们的查询。可以在MSDN上询问到。使用记录工具保存这个查询。若是它是三个用来保存注释或一些的工具,可以让您在任啥地点方轻松地取得查询,那么它将会怀有支持。一段时间后,就足以依据使用要求对这个查询稍作修改。然后,不必要在object
browser窗格中查找表列表,您很快就可以从集合中取得适当的查询,执行它,并很快获取新闻。

摸索触发器的代码

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; 

 

结果如图:

997755.com澳门葡京 4

 

7个引用正在履行那些历程。我们在sys.SQL_modules中查找了独具的概念可以找到1个特定的字符串,那种艺术很慢很暴力,可是它是可行的!

  使用 OBJECT_ID 无法查询非架构范围内的靶子(如 DDL 触发器)。对于在
sys.objects
目录视图中找不到的目的,须求经过查询适当的目录视图来取得该目标的标识号。例如,若要再次来到DDL 触发器的目的标识号,请使用 SELECT OBJECT_ID FROM sys.triggers WHERE
name = ‘DatabaseTriggerLog’语法:

系统存储进程与系统函数在系统表和元数据里面提供了贰个抽象层,使得大家不要直接询问系统表就能获取当前数据库对象的元数据。

相比较灵通的查询实例

上面小编会浮现的事例都已经在二零零六和2012七个本子中测试。当然只用到了各自版本的终极一个版本更新后的数据库。

下图中呈现了拥有继续sys.objects列的视图。这表示它们除了具有那个列以外,还有列的对应品种。那是视图所有的音讯比如create_date也都来自sys.objects

 997755.com澳门葡京 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'); 

 

在具备目的中检索字符串

本身想明白除了触发器之外是或不是还有其余对象调用这几个进程?大家多少修改查询以寻找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; 

查询结果如下图:

997755.com澳门葡京 6

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

1 OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] 
2     object_name' [ ,'object_type' ] )

常用的与元数据有关的连串存储进度有以下一些:

总结

  如上,到那级别简单实用丰硕了。们早已介绍了一般的答辩,并介绍了寻找数据库中的内容的主题情势。在下一篇中本人将会深深介绍触发器并且找到有效音讯的排序以有利于可以经过系统视图从动态在线目录中收载的有用音信。

 

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

大家得以经过系统视图驾驭它们啊?嗯,是的。以下是列出服务器触发器及其定义的说话

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

留意,只可以见到有权力看的触发器

参数:

系统存储进度

总结

  本文切磋过触发器,并且你能摸清触发器,以及地下的难题。那里并没有对准有关触发器的询问提供贰个周密的工具箱,因为本身只是采用触发器作为示范来体今后询问系统视图时大概使用的部分技能。在大家上学了目录、列和参数之后,大家将赶回触发器,并精通了编辑访问系统视图和information
schema视图的询问的片段家常用途。表是元数据的许多地点的功底。它们是两种档次的靶子的父类,其他元数据如索引是表的习性。大家正在逐年地大力去发现持有关于表的信息。期待下期

   ‘ object_name ‘要使用的目的。object_name 的数据类型为 varchar 或
nvarchar。倘若 object_name 的数据类型为 varchar,则它将隐式转换为
nvarchar。可以挑选是不是指定数据库和架构名称。
  ’ object_type ‘架构范围的目标类型。object_type 的数据类型为
varchar 或 nvarchar。若是 object_type 的数据类型为
varchar,则它将隐式转换为 nvarchar。有关对象类型的列表,请参阅
sys.objects (Transact-SQL) 中的 type 列。
重回类型:int

◆sp_columns 再次回到指定表或视图的列的详细新闻。

示例:

◆sp_databases 重返当前服务器上的兼具数据库的主干消息。

A. 重临指定对象的目的 ID

◆sp_fkeys
若参数为含有主键的表,则赶回包含指向该表的外键的所有表;若参数为带有外键的表名,则赶回所有同过主键/外键关系与该外键相关联的所有表。

1 USE master;
2 GO
3 SELECT OBJECT_ID(N'AdventureWorks.Production.WorkOrder') AS 'Object ID';
4 GO

◆sp_pkeys 重返指定表的主键新闻。

B. 验证目标是还是不是留存

◆sp_server_info 再次来到当前服务器的各个风味及其相应取值。

1 USE AdventureWorks;
2 GO
3 IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
4 DROP TABLE dbo.AWBuildVersion;
5 GO

◆sp_sproc_columns 再次来到指定存储进度的的输入、输出参数的音信。

别的表明:SQL中object_id函数的用法

 

语法:OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] object_name' [ ,'object_type' ] )

 

参数

 

 object_name 
要运用的对象。object_name 的数据类型为 varchar 或 nvarchar。如果 object_name 的数据类型为 varchar,则它将隐式转换为 nvarchar。能够挑选是不是指定数据库和架构名称。

 object_type 
架构范围的对象类型。object_type 的数据类型为 varchar 或 nvarchar。如果 object_type 的数据类型为 varchar,则它将隐式转换为 nvarchar。有关对象类型的列表,请参阅 sys.objects
(Transact-SQL) 中的 type 列。

回到类型 :int

997755.com澳门葡京 7异常:

 

对此空间引得,OBJECT_ID 返回 NULL。

并发谬误时,重临 NULL。

用户只好查看其拥有的平安目的的元数据,只怕已对其授予权限的雅安目标的元数据。也等于说,如若用户对该对象没有任何权力,则有些会暴发元数据的嵌入函数(如
OBJECT_ID)只怕回到
NULL。有关详细新闻,请参阅 元数据可知性配置和 元数据可知性故障排除。

997755.com澳门葡京 8

注释 :

 

当该参数对系统函数可选时,则系统使用当下数据库、主机、服务器用户或数据库用户。内置函数前面总得跟圆括号。

当指定临时表名时,除非当前数据库为 tempdb,否则必须在该临时表名从前增进数据库名称。例如:SELECT OBJECT_ID('tempdb..#mytemptable')

系统函数可以在甄选列表、WHERE
子句和其他允许采用表明式的地方采纳。有关详细音信,请参阅 表达式(Transact-SQL)和 WHERE
(Transact-SQL)。

 

分类: 数据库

◆sp_statistics 再次来到指定的表或索引视图上的具有索引以及计算的音信。

◆sp_stored_procedures 再次回到当前数据库的储存进程列表,包括系统存储进度。

◆sp_tables 再次回到当前数据库的所有表和视图,包蕴系统表。

常用的与元数据有关的连串函数有以下一些:

系统函数

◆COLUMNPROPE大切诺基TY
重临有关列或进程参数的音讯,如是不是同意空值,是或不是为统计列等。

◆COL_LENGTH 重返指定数据库的指定属性值,如是或不是处在只读方式等。

◆DATABASEPROPE锐界TYEX
重回指定数据库的指定选项或性质的当下设置,如数据库的情形、苏醒模型等。

◆OBJECT_ID 再次回到指定数据库对象名的标识号

◆OBJECT_NAME 重返指定数据库对象标识号的靶子名。

◆OBJECTPROPE哈弗TY
重返指定数据库对象标识号的有关消息,如是还是不是为表,是或不是为约束等。

◆fn_listextendedproperty
再次回到数据库对象的恢弘属性值,如目的描述、格式规则、输入掩码等。

由于大家鞭长莫及直接运用到存储进度与函数的回来结果,因而只有在我们关注的只是询问的结果,而不需求越来越运用那个结果的时候,大家会动用系统存储进度与系统函数来查询元数据。

比如,倘使要获取当前服务器上有所数据库的基本消息,大家得以在查询分析器里面运维:

EXEC sp_databases
GO
 
在回来结果中大家能够观看数据库的称号、大小及备注等音讯。

而是倘使要引用那部分新闻,只怕存储那有个别音讯以供前边使用,那么大家无法不倚重中间表来达成那几个操作:

 

以下为引用的内容:

CREATE TABLE #sp_result
(
DATABASE_NAME sysname,
DATABASE_SIZE int,
REMARKS varchar(254) NULL
)
GO
INSERT INTO #sp_result
EXEC (‘sp_databases’)
GO

行使消息架构视图访问元数据

音讯架构视图基于 SQL-92
标准中针对架构视图的概念,那一个视图独立于系统表,提供了关于 SQL Server
元数据的里边视图。音信架构视图的最大亮点是,尽管大家对系统表进行了关键的修改,应用程序也得以健康地应用这么些视图举行访问。因而对于应用程序来说,只如果契合
SQL-92 标准的数据库系统,使用音讯架构视图总是可以不奇怪办事的。

音讯架构视图

◆INFORMATION_SCHEMA.CHECK_CONSTRAINTS:重回有关列或进度参数的新闻,如是还是不是同意空值,是还是不是为统计列等。

◆INFORMATION_SCHEMA.COLUMNS:重临当前数据库中当前用户可以访问的所有列及其宗旨音信。

◆INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE:再次回到当前数据库中定义了封锁的所有列及其约束名。

◆INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE:再次回到当前数据库中定义了封锁的所有表及其约束名。

◆INFORMATION_SCHEMA.KEY_COLUMN_USAGE:重返当前数据库中作为主键/外键约束的所有列。

◆INFORMATION_SCHEMA.SCHEMATA:再次来到当前用户拥有权限的享有数据库及其主旨音信。

◆INFORMATION_SCHEMA.TABLES:重返当前用户所有权限的当前数据库中的所有表或许视图及其大旨新闻。

◆INFORMATION_SCHEMA.VIEWS:重返当前数据库中的当前用户可以访问的视图及其主人、定义等新闻。

是因为那一个音信架构都是以视图的艺术存在的,由此我们得以很有益于地收获并利用须要的新闻。

诸如,我们要博取有些表有多少列,可以动用以下语句:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=’mytable’

使用系统表访问元数据

虽说应用系统存储进度、系统函数与消息架构视图已经得以为大家提供了一定充足的元数据音信,不过对于有些特殊的元数据音信,大家照例须要直接对系统表举办询问。因为SQL
Server 将具备数据库对象的音信均存放在系统表中,作为 SQL Server
的治本、开发人士,精通种种系统表的效率将力促我们询问 SQL Server
的内在工作规律。

SQL Server 的系统表相当多,其中最常用的与元数据查询有关的表有如下一些:

syscolumns
存储每一种表和视图中的每一列的音讯以及存储进程中的每一种参数的新闻。

syscomments 存储包涵逐个视图、规则、专擅认同值、触发器、CHECK 约束、DEFAULT
约束和仓储进度的原始 SQL 文本语句。

sysconstraints 存储当前数据库中每1个封锁的骨干音信。

sysdatabases 存储当前服务器上每1个数据库的着力新闻。

sysindexes 存储当前数据库中的每种索引的新闻。

sysobjects
存储数据库内的各类对象(约束、默许值、日志、规则、存储进程等)的骨干新闻。

sysreferences 存储所有包涵 FOREIGN KEY 约束的列。

systypes 存储系统提供的每一个数据类型和用户定义数据类型的详细新闻。

将系统存储进度、系统函数、音讯架构视图与系统表组成使用,可以方便地让大家赢得所有必要的元数据消息。

示例:

1、 拿到当前数据库所有用户表的名号。

SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = ‘U’ AND OBJECTPROPERTY (id, ‘IsMSShipped’) = 0
 
其间第一接纳了系统表 sysobjects以及其属性 xtype,还有尽管使用了
OBJECTPROPE福特ExplorerTY 系统函数来判定是还是不是设置 SQL Server 的进度中创设的靶子。

二,得到指定表上所有的目录名称。

SELECT name FROM sysindexes
WHERE id = OBJECT_ID (‘mytable’) AND indid > 0
 
综上所述实例

上面给出了一个囤积进度,它的法力是自行将近来数据库的用户存储进度加密。

 

以下为引用的内容:

DECLARE @sp_name nvarchar(400)
DECLARE @sp_content nvarchar(2000)
DECLARE @asbegin int
declare @now datetime
select @now = getdate()
DECLARE sp_cursor CURSOR FOR
SELECT object_name(id)
FROM sysobjects
WHERE xtype = ‘P’
AND type = ‘P’
AND crdate < @now
AND OBJECTPROPERTY(id, ‘IsMSShipped’)=0

OPEN sp_cursor

FETCH NEXT FROM sp_cursor
INTO @sp_name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM
syscomments WHERE id = OBJECT_ID(@sp_name)
SELECT @asbegin =
 PATINDEX ( ‘%AS’ + char(13) + ‘%’, @sp_content)
SELECT @sp_content =
SUBSTRING(@sp_content, 1, @asbegin – 1)
+ ‘ WITH ENCRYPTION AS’
+ SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))
SELECT @sp_name = ‘DROP PROCEDURE [‘ + @sp_name + ‘]’
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCH NEXT FROM sp_cursor
INTO @sp_name
END

CLOSE sp_cursor
DEALLOCATE sp_cursor

 
该存储进度接纳了 sysobjects 和 syscomments
表,并巧妙地修改了原存储进程的 SQL 定义语句,将 AS 修改为了 WITH
ENC讴歌ZDXYPTION AS,从而达到了加密存储进度的目标。本存储进度在 SQL Server
三千 上经过。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website