深究SQL Server元数据(二)

发布时间:2019-08-24  栏目:NoSQL  评论:0 Comments

背景

在率先篇中自身介绍了怎么着访谈元数据,元数据为何在数据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;假如该表是五个堆(那只是意味着该表未有聚焦索引的另一种格局),则会有一行的index_id值为0。另外,无论该表是或不是有聚焦索引,每种非聚焦索引都有一行,其index_id值大于1。我们过滤了的目录,那些索引是由数据库引擎优化顾问(DTA)创造的,目标只有是测量试验一个可能的目录是不是可行。避防它们积攒起来,最佳把它们去掉。

一旦您过贰个多少个钦点的表,下边包车型地铁那一个查询是尤其合理的,需求在上头的例子中追加对象的钦点:

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’来探究你想要的视图名称。.

在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;

各种表中有微微个目录,并显示他们的名字

前面包车型客车表并不特地有用,因为不可能一眼看出各个表有多少索引,以及它们是什么。上面这几个讲话能够兑现:

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 中的对象包蕴以下:聚合的CL奥迪Q7函数,check
约束,SQL标量函数,CLLAND标量函数,CLRubicon表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CL君越存款和储蓄过程,布置指南,主键约束,老式准绳,复制过滤程序,系统基础表,同义词,类别对象,服务队列,CLRAV4DML
触发器,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()告诉自个儿数据库的称呼。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中华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中的文书档案,查看里面包车型客车一个文书档案是或不是有利于元数据查询,总是值得检查的。

询问更新过的索引缺未有应用过有哪些?

连年有供给找寻自上次起动服务器来讲未曾行使的目录,非常是一旦服务器一贯在做各式各样的做事时。

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

那么些触发器访问了稍稍对象

在代码中,各样触发器要会见多少对象(譬喻表和函数)?

我们只需求检查表达式信赖项。那几个查询利用一个视图来列出“软”依赖项(如触发器、视图和函数)。

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和数据库开垦的做事中变得进一步首要了,

留下评论

网站地图xml地图