探索SQL Server元数据(三):索引元数据

澳门新葡亰亚洲在线 10

背景

在第风流倜傥篇中作者介绍了怎么访谈元数据,元数据为啥在数据库里面,以至怎么样利用元数据。介绍了什么识破各类数据库对象的在数据Curry面包车型地铁名字。第二篇,小编接收了触发器的大旨,因为它是一个能提供很好例子的数据库对象,并且在此个目的中可以看到建议难题和消除难点。

本篇小编将会介绍元数据中的索引,不止是因为它们自个儿很注重,更重视的是它们是很好的元数据类型,比如列恐怕遍布总括,那几个不是元数据中的对象。

目录对于其他关周详据库表都以少不了的。然则,就像是吐司上的黄油同样,过度施用它们也许会在数据库中爆发难点。偶然,能够对表实行过度索引或缺点和失误索引,恐怕创设重复索引。一时难题是接纳贰个坏的填写因子,错误地设置ignore_dup_key选项,创立贰个世代不会被利用(但不得不被爱抚卡塔尔(قطر‎的目录,错失外键上的目录,恐怕将GUID作为主键的黄金年代有个别。由此可以看到,任何频仍利用的数据库系统中的索引都急需定期维护和表明,而目录视图是瓜熟蒂落那一个干活儿的最直白的秘诀之意气风发。

背景

  上大器晚成篇中,笔者介绍了SQL Server
允许访谈数据库的元数据,为啥有元数据,怎么样行使元数据。那大器晚成篇中笔者会介绍怎样进一层找到各个有价值的新闻。以触发器为例,因为它们往往一齐比超多主题素材。

 

都有何样索引能够查到?

让大家经过上面包车型客车大致语句来看一下都有哪些索引在您的数据库上,代码如下:

SELECT  convert(CHAR(50),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The Table', i.name AS index_name
FROM sys.indexes AS i
  INNER JOIN sys.tables t
    ON t.object_id=i.object_id
  WHERE is_hypothetical = 0 AND i.index_id <> 0;

结果如下:

澳门新葡亰亚洲在线 1

为什么要去援用sys.tables?那是因为它是保险只获得顾客表的最简便易行方法。大家选用index_id
的values大于0,因为只要不为表制造集群索引,在sys中仍有三个条约。索引,但它指向的是堆,不表示索引。每一种表在sys中都有生龙活虎行。索引值为0或1的目录。假如该表有一个集中索引,则有风华正茂行数据且index_id值为1;即便该表是叁个堆(那只是表示该表未有聚焦索引的另生龙活虎种办法卡塔尔(英语:State of Qatar),则会有生龙活虎行的index_id值为0。别的,无论该表是或不是有聚焦索引,每一个非聚焦索引都有黄金年代行,其index_id值大于1。大家过滤了的目录,那几个索引是由数据库引擎优化奇士智囊团(DTA卡塔尔(英语:State of Qatar)创设的,指标只有是测验叁个可能的目录是或不是可行。避防它们储存起来,最棒把它们去掉。

假若您过三个四个钦定的表,下边包车型大巴那个查询是进一层合理的,须要在地方的事例中扩张对象的钦赐:

AND t.object_id = OBJECT_ID('Production.BillOfMaterials');

 

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

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

种种表中有稍许个目录,并出示他们的名字

前边的表并不特意有用,因为不能够一眼看出种种表有多少索引,以至它们是怎么。上边这几个讲话能够兑现:

SELECT  convert(CHAR(20),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The_Table',
sum(CASE WHEN i.object_ID IS NULL THEN 0 ELSE 1 END) AS The_Count,
coalesce(stuff(( 
     SELECT ', '+i2.name
       FROM sys.indexes i2
       WHERE t.object_ID = i2.object_ID
       ORDER BY i2.name
     FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,''),'') AS Index_List
  FROM sys.tables AS t
  LEFT OUTER JOIN sys.indexes i
    ON t.object_id=i.object_id
      AND is_hypothetical = 0 AND i.index_id > 0 
GROUP BY t.Object_ID;

 

自个儿在老的测量试验数据库上实行这几个测量试验,对象名称不够长。

The_Table            The_Count   Index_List
-------------------- ----------- --------------------------------------------------
dbo.publishers       1           UPKCL_pubind
dbo.titles           2           titleind, UPKCL_titleidind
dbo.titleauthor      3           auidind, titleidind, UPKCL_taind
dbo.stores           1           UPK_storeid
dbo.sales            2           titleidind, UPKCL_sales
dbo.roysched         1           titleidind
dbo.discounts        0           
dbo.jobs             1           PK__jobs__6E32B6A51A14E395
dbo.pub_info         1           UPKCL_pubinfo
dbo.employee         2           employee_ind, PK_emp_id
dbo.authors          2           aunmind, UPKCL_auidind

(11 row(s) affected)

可是当然三个触发器是首先是三个指标,因而一定在sys.objects?

  在大家选取sys.triggers的音信从前,必要来再度贰次,全体的数据库对象都留存于sys.objects中,在SQL
Server 中的对象包罗以下:聚合的CLLX570函数,check
限定,SQL标量函数,CLMurano标量函数,CLLX570表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CL奥迪Q5存款和储蓄进度,布置指南,主键限定,老式准则,复制过滤程序,系统根基表,同义词,类别对象,服务队列,CLMuranoDML
触发器,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中的。现在将在带大家一了百了襲找找这个新闻。

寻找未有聚集索引的表

有关索引,您能够找到非常多风趣的事物。举个例子,这里有风流倜傥种高效查找表的秘诀,无需使用集中索引(堆卡塔尔

-- 展示所有没有聚集索引的表名称
SELECT  object_schema_name(sys.tables.object_id)+'.'
    +object_name(sys.tables.object_id) AS 'Heaps'
      FROM sys.indexes /* see whether the table is a heap */
      INNER JOIN sys.tables ON sys.tables.object_ID=sys.indexes.object_ID
      WHERE sys.indexes.type = 0;

触发器的主题素材

  触发器是有效的,可是因为它们在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触发器关联,稍后我们将领会。

种种索引中有稍许行在表里面?

透过延续sys.partitions视图,我们得以测算出索引中山大学约有个别许行。小编改善了有个别代码,关联了sys.extended_properties,那样能够把备注的音信带出来。

--列出每个索引/堆的行数
SELECT 
  OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) as 'Table',
  coalesce(i.NAME,'(IAM for heap)') as 'Index',
  Coalesce(
   (SELECT SUM(s.rows) FROM sys.partitions s WHERE s.object_id = i.object_id
        AND s.index_id = i.index_ID    
    ), 0) 'Rows',coalesce(ep.Value,'') as comments
 FROM sys.tables t
   INNER JOIN sys.indexes i ON i.object_id = t.object_id
   LEFT OUTER JOIN sys.Extended_Properties ep
   ON i.Object_Id = ep.Major_Id AND i.Index_Id = Minor_Id AND Class = 7;

 

澳门新葡亰亚洲在线 2

然后,你能够修改这几个代码,让其只是彰显每一个在索引表中的表有多少行。

SELECT
  OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) AS 'Table',  
  sum(rows) AS row_count
FROM sys.partitions p INNER JOIN sys.tables t 
   ON p.object_ID=t.object_ID 
WHERE index_id < 2 GROUP BY t.object_ID,Index_ID;

在数据库中列出触发器

那么怎么获取触发器列表?下边笔者在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(卡塔尔(英语:State of Qatar)告诉本人数据库的名目。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

 

表中都有超级多索引吗?

如若你对少数表具备大量索引认为匪夷所思,那么能够运用上面查询,该查询告诉您具备当先4个目录和索引计数超越列计数百分之四十的表。它是意气风发种任性接纳具备多量索引的表的艺术。

--超过4个索引的表 
--索引个数超过列数一半
SELECT object_schema_name(TheIndexes.Object_ID) + '.'+ object_name(TheIndexes.Object_ID) AS TableName,
       Columns, Indexes 
 FROM 
   (SELECT count(*) AS indexes, t.object_ID
      FROM sys.indexes i
      INNER JOIN sys.tables t
        ON i.object_ID=t.object_ID 
    GROUP BY t.object_ID) TheIndexes
 INNER JOIN
  (SELECT count(*) AS columns, t.object_ID
     FROM sys.columns c
       INNER JOIN sys.tables t
     ON c.object_ID=t.object_ID 
   GROUP BY t.object_ID)TheColumns
 ON TheIndexes.object_ID=TheColumns.object_ID
 WHERE indexes>columns/2 AND indexes>4;

 

笔者的表和视图有多少个触发器?

自己想领悟各类表有多少个触发器,并且什么景况下接触它们。上边大家列出了具备触发器的表以至各样事件的触发器数量。各个表或许视图对于触发器行为都有贰个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’来承认谁是最后叁个实行的触发器
。为了获得第贰个触发器,酌情接收ObjectPropertyEx()
元数据函数,须求输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 大概 ‘ExecIsFirstUpdateTrigger’。

进而我们几近些日子明白了表有哪些触发器,哪些事件触发这个触发器。能够利用objectpropertyex()元数据函数,那些函数再次来到非常多不后生可畏音讯,依据钦赐的参数差别。通过翻看MSDN中的文书档案,查看里面包车型地铁二个文书档案是还是不是有扶持元数据查询,总是值得检查的。

询问更新过的索引缺未有行使过有怎么着?

老是有不能够贫乏搜索自上次运维服务器来说从未利用的目录,特别是蓬蓬勃勃旦服务器平昔在做多姿多彩的做事时。

--Indexes updated but not read.
SELECT
    object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) as Thetable,
    i.name    AS 'Index'
  FROM sys.indexes i
    left outer join sys.dm_db_index_usage_stats s 
      ON s.object_id = i.object_id
       AND s.index_id = i.index_id
       AND s.database_id = DB_ID()
  WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.index_id > 0  --Exclude heaps.
    AND i.is_primary_key = 0 --and Exclude primary keys.
    AND i.is_unique = 0    --and Exclude unique constraints.
    AND coalesce(s.user_lookups + s.user_scans + s.user_seeks,0) = 0 --No user reads.
    AND coalesce(s.user_updates,0) > 0; --Index is being updated.

 

专一:作者生机勃勃度在代码里采纳了动态管理视图sys.dm_db_index_usage_stats,这里起到了手提式有线电话机使用音信的作用,之后大家会更详尽的施用换这么些指标来证实其效力。

触发器何时触发事件?

让我们看一下这么些触发器,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接纳相关子查询来询问那个事件。

这么些索引占用了稍微空间?

举例希图知道索引占了多少空间,有成都百货上千‘胖’索引,正是满含了成都百货上千列,有希望索引中部分列不会鬼使神差在别的查询中,那就是萧条了空间。

SELECT 
  object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) AS Thetable,
  coalesce(i.name,'heap IAM')    AS 'Index',
  convert(DECIMAL(9,2),(sum(a.total_pages) * 8.00) / 1024.00)    AS 'Index_MB'
FROM sys.indexes i
INNER JOIN sys.partitions p 
  ON i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
  ON p.partition_id = a.container_id
  WHERE objectproperty(i.object_id, 'IsUserTable') = 1
GROUP BY i.object_id, i.index_id, i.name;

澳门新葡亰亚洲在线, 

澳门新葡亰亚洲在线 3

触发器的多少长度?

成都百货上千数据库职员不援救冗长触发器的定义,但他们或许会意识,根据定义的长度排序的触发器列表是探究数据库的风流洒脱种有用艺术。

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

 

好啊,小编或然太叱责了,不太中意太长的,然则逻辑不时候会非常短。事实上,前三名在小编眼里是不可信赖的,即使自个儿总是趋势于尽大概少地接受触发器。

测算表总的目录空间

让大家看看每一个表的总索引空间,以致表中的行数。

SELECT 
  object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) AS Thetable,
  convert(DECIMAL(9,2),(sum(a.total_pages) * 8.00) / 1024.00)    AS 'Index_MB',
  max(row_count) AS 'Rows',
  count(*) AS Index_count
FROM sys.indexes i
INNER JOIN
  (SELECT object_ID,Index_ID, sum(rows) AS Row_count 
     FROM sys.partitions GROUP BY object_ID,Index_ID)f
  ON f.object_ID=i.object_ID AND f.index_ID=i.index_ID
INNER JOIN sys.partitions p 
  ON i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
  ON p.partition_id = a.container_id
  WHERE objectproperty(i.object_id, 'IsUserTable') = 1
GROUP BY i.object_id;

澳门新葡亰亚洲在线 4

那个触发器访问了稍稍对象

在代码中,每一个触发器要访谈多少对象(举个例子表和函数卡塔尔?

我们只需求检查表明式正视项。这几个查询利用一个视图来列出“软”信赖项(如触发器、视图和函数卡塔尔(英语:State of Qatar)。

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来实在看一下,有啥样信赖。

何以查询表使用索引的各个办法?

察觉有关索引的一点品质,经常最好利用品质函数作为火速形式。

-- 查询没有主键的表
SELECT  object_schema_name(object_id)+'.'+object_name(object_id) as No_Primary_key
  FROM sys.tables/* see whether the table has a primary key */
  WHERE objectproperty(OBJECT_ID,'TableHasPrimaryKey') = 0;


-- 查询没有索引的表
SELECT  object_schema_name(object_id)+'.'+object_name(object_id) as No_Indexes  
  FROM sys.tables /* see whether the table has any index */
  WHERE objectproperty(OBJECT_ID,'TableHasIndex') = 0;


-- )查询没有候选键的表
SELECT  object_schema_name(object_id)+'.'+object_name(object_id) as No_Candidate_Key
  FROM sys.tables/* if no unique constraint then it isn't relational */
  WHERE objectproperty(OBJECT_ID,'TableHasUniqueCnst') = 0
    AND   objectproperty(OBJECT_ID,'TableHasPrimaryKey') = 0;


--查询带有禁用索引的表
SELECT  distinct
  object_schema_name(object_id)+'.'+object_name(object_id) as Has_Disabled_indexes
  FROM sys.indexes /* don't leave these lying around */
  WHERE is_disabled=1;

特定触发器访谈照旧写入哪些对象?

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

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

 

那个是指标,那个不是?

你也许注意到了一些想不到的事务。就算表的部分性质(如主键卡塔尔国本人正是目的,但列、计算或索引实际不是对象。让大家弄驾驭那一点,因为它不是一点一滴直观的反映在sys.objects,您能够找到有关全体国有数据库组件的主导规范信息,如表、视图、同义词、外键、检查节制、键节制、默许限定、服务队列、触发器和进度。笔者列出的装有这个构件都有任何质量,那个属性必需通过三番若干回相关主旨个性的视图可以预知,但也囊括与目的相关的数据列。最佳利用那些极度的视图,因为它们有你需求的具备音信,系统只过滤您感兴趣的目的类型,比如表。各类对象(如节制和触发器卡塔尔在sys.objects中都有parent_ID,非零的目的表,展现它们是子对象。

下边包车型客车询问向您彰显了生机勃勃种查看那一个子对象并将其与养父母关系的简约方法。

--查询索引父对象(表名)和索引名称
SELECT parent.name AS Parents_name, 
       child.name AS Childs_Name, 
       replace(lower(parent.type_desc),'_',' ') AS Parents_type, 
       replace(lower(child.type_desc),'_',' ') AS Childs_type
FROM sys.objects child
  INNER JOIN sys.objects parent
    ON parent.object_ID=child.parent_object_id
WHERE child.parent_object_id<>0
ORDER BY parents_name;

 

澳门新葡亰亚洲在线 5.

你会发觉索引不是目的。在率先个查询中,重返的object_ID是定义索引的表的ID。

此地的难点是关系是头眼昏花的。节制能够包罗几个列,也可以由索引逼迫。索引能够分包多少个列,可是种种很主要。总结数据还足以饱含多少个列,也能够与索引相关联。那意sys.indexes,
sys.stats and
sys.columns不从sys.objects世襲。参数和花色也是如此。

触发器里有啥代码?

现在让我们由此检查触发器的源代码来确认这点。.

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

什么样询问每三个表的每叁个目录的每七个列?

最简便易行的查询艺术如下:

SELECT object_schema_name(t.object_ID)+'.'+t.name AS The_Table, --the name of the table
       i.name AS The_Index,  -- its index
       index_column_id,
       col_name(Ic.Object_Id, Ic.Column_Id) AS The_Column --the column
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.object_ID=i.object_ID
INNER JOIN sys.Index_columns  ic
    ON i.Object_ID=ic.Object_ID
    AND i.index_ID=ic.index_ID
ORDER BY t.name,i.index_id, index_column_id;

 

 

澳门新葡亰亚洲在线 6

当然也得以钦命特定表,比方:

  WHERE i.object_id = OBJECT_ID('Production.BillOfMaterials');

检索触发器的代码

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; 

 

结果如图:

澳门新葡亰亚洲在线 7

 

8个引用正在实施这么些进度。大家在sys.SQL_modules中搜寻了具有的概念能够找到一个特定的字符串,这种办法非常慢很暴力,不过它是平价的!

目录中有怎么着列,顺序又是怎么样 ?

也能够集中上边语句,种种索引汇总成风流倜傥行,浮现全部索引,具体代码如下:

SELECT object_schema_name(t.object_ID)+'.'+t.name AS The_Table, --the name of the table
   coalesce(stuff (--get a list of indexes
     (SELECT ', '+i.name
     +' ( '
         +stuff (--get a list of columns
         (SELECT ', ' + col_name(Ic.Object_Id, Ic.Column_Id) 
         FROM  sys.Index_columns  ic
         WHERE ic.Object_ID=i.Object_ID
         AND ic.index_ID=i.index_ID
         ORDER BY index_column_ID ASC
         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') +' )'
     FROM sys.indexes i 
     WHERE i.object_ID=t.object_ID
     FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,''),'') AS Indexes
 FROM sys.tables t;

功效如下:

澳门新葡亰亚洲在线 8

在具有目的中检索字符串

自己想掌握除了触发器之外是不是还只怕有此外对象调用那几个进程?我们稍事校正查询以寻觅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; 

询问结果如下图:

澳门新葡亰亚洲在线 9

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

哪些查询XML索引?

XML索引被视为索引的强大。笔者意识查看其细节的最棒办法是为它们构建四个CREATE语句。

SELECT 'CREATE' + case when secondary_type is null then ' PRIMARY' else '' end
 + ' XML INDEX '+coalesce(xi.name,'')+ '  
    ON ' --what table and column is this XML index on?
 + object_schema_name(ic.Object_ID)+'.'+object_name(ic.Object_ID)
 +' ('+col_name(Ic.Object_Id, Ic.Column_Id)+' )  
    '+ coalesce('USING XML INDEX [' + Using.Name + '] FOR ' + Secondary_Type_DeSc
     COLLATE database_default,'')    
    +'  
'+      replace('WITH ( ' + 
   stuff(
  CASE WHEN xi.Is_Padded <> 0 THEN ', PAD_INDEX  = ON ' ELSE '' END 
  + CASE 
     WHEN xi.Fill_Factor NOT IN (0, 100) 
        THEN ', FILLFACTOR  =' + convert(VARCHAR(3), xi.Fill_Factor) + ''
        ELSE '' END 
  + CASE WHEN xi.Ignore_dUp_Key <> 0 THEN ', IGNORE_DUP_KEY = ON' ELSE '' END 
  + CASE WHEN xi.Allow_Row_Locks = 0 THEN ', ALLOW_ROW_LOCKS  = OFF' ELSE '' END 
  + CASE WHEN xi.Allow_Page_Locks = 0 THEN ', ALLOW_PAGE_LOCKS  = OFF' ELSE ' ' END
   , 1, 1, '')
 + ')', 'WITH ( )', '') --create the list of xml index options
+  coalesce('/* '+convert(varchar(8000),Value)+ '*/','')--and any comment
    AS BuildScript
FROM sys.xml_Indexes xi
      inner join sys.index_columns ic 
   ON ic.Index_Id = xi.Index_Id
   AND ic.Object_Id = xi.Object_Id   
  LEFT OUTER JOIN sys.Indexes [USING]
   ON [USING].Index_Id = xi.UsIng_xml_Index_Id
   AND [USING].Object_Id = xi.Object_Id
  LEFT OUTER JOIN sys.Extended_Properties ep
   ON ic.Object_Id = ep.Major_Id AND ic.Index_Id = Minor_Id AND Class = 7
WHERE object_schema_name(ic.Object_ID) <>'sys' AND ic.index_id>0;

上边包车型大巴查询结果将显得全部骨干的XML索引细节作为创设脚本。

澳门新葡亰亚洲在线 10

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

我们得以经过系统视图领会它们啊?嗯,是的。以下是列出服务器触发器及其定义的言辞

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

在意,只好看看有权力看的触发器

元数据中还应该有别的品种的目录吗?

再有三种相比较独特的目录,一是空中引得,其音讯在sys.spatial_index_tessellations

sys.spatial_indexes表中。另二个是全文索引,其消息在fulltext_index_fragments,fulltext_index_catalog_usages, fulltext_index_columns
fulltext_indexes表中保留。**

总结

  本文切磋过触发器,并且你能摸清触发器,以致地下的难点。这里并从未针对性有关触发器的询问提供贰个统筹的工具箱,因为自个儿只是利用触发器作为示范来显示在询问系统视图时大概使用的有的技艺。在大家上学了目录、列和参数之后,大家将再次来到触发器,并打听了编写制定访谈系统视图和information
schema视图的询问的风度翩翩部分常备用项。表是元数据的不菲上面的底蕴。它们是两种档期的顺序的靶子的父类,别的元数据如索引是表的质量。大家正在日益地质大学力去开采具备有关表的新闻。期望上一期

商量索引总结信息

几天前,让我们斟酌一下布满总括数据或“stats”。各样索引都有三个附加的总计对象,以便查询优化器能够提供一个确切的查询布置。为此,它供给估算数据的“基数”,以明显为别的索引值重临多少行,并使用那些“stats”对象告诉它多少是如何布满的。

能够查询总结音信目的是哪些与表举行关联的,语句如下:

SELECT object_schema_name(t.Object_ID) + '.'+ t.name AS The_table, 
    stats.name AS Stats_Name, sys.columns.name AS Column_Name
  FROM sys.stats
 INNER JOIN sys.stats_columns
    ON stats.object_id = stats_columns.object_id
   AND stats.stats_id = stats_columns.stats_id
 INNER JOIN sys.columns
    ON stats_columns.object_id = columns.object_id
   AND stats_columns.column_id = columns.column_id
 INNER JOIN sys.tables t
    ON stats.object_id = t.object_id;

 

当它们与索引相关联时,总括数据世襲索引的名号,并接受与索引相像的列。

澳门新葡亰亚洲在线 11

自己商议重复的计算音讯

通过比较与各个总括音讯相关联的列号列表,您能够超快查看同一列或黄金年代组列是或不是有三个总括音讯。

SELECT object_schema_name(Object_ID)+'.'+object_name(Object_ID) as tableName,
       count(*) as Similar, ColumnList as TheColumn, 
       max(name)+', '+min(name) as duplicates
FROM 
   (SELECT Object_ID, name,   
     stuff (--get a list of columns
         (SELECT ', ' + col_name(sc.Object_Id, sc.Column_Id)
         FROM  sys.stats_columns  sc
         WHERE sc.Object_ID=s.Object_ID
         AND sc.stats_ID=s.stats_ID
         ORDER BY stats_column_ID ASC
         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS ColumnList
   FROM sys.stats s)f
GROUP BY Object_ID,ColumnList 
HAVING count(*) >1;

结果如下:

澳门新葡亰亚洲在线 12

来得了蕴藏重复的总计对象,在本例中是sales.customer表在AccountNumber列上有多少个八九不离十的总括对象。

总结

 在数据库中有广大有价值的新闻都在目录上。豆蔻梢头旦表的数量变大,非常轻巧让表出现实形势部主题素材,举例无意中从不集中索引或主键,或然有再一次的目录或不供给的计算音信等。大家经过明白哪些询问那些索引的动态视图后能够比相当慢查询定位使用表的音讯,方便大家预防和减轻那类难题,这几个底蕴措施已经在DBA和数据库开辟的办事中变得更其首要了,

发表评论

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