mg4377娱乐娱城官网_mg4377娱乐手机版_www.mg4377.com

当前位置: mg4377娱乐娱城官网 > mg > 正文

索引元数据,Server数据库元数据

时间:2019-08-03 12:11来源:mg
背景 上一篇中,笔者介绍了SQL Server允许访谈数据库的元数据,为啥有元数据,怎么样采纳元数据。这一篇中作者会介绍怎么样进一步找到各类有价值的信息。以触发器为例,因为它们往

背景

  上一篇中,笔者介绍了SQL Server 允许访谈数据库的元数据,为啥有元数据,怎么样采纳元数据。这一篇中作者会介绍怎么样进一步找到各类有价值的信息。以触发器为例,因为它们往往一齐很多标题。

 

简介

  在数据库中,大家除了存款和储蓄数据外,还蕴藏了汪洋的元数据。它们重要的功力正是描述数据库怎么创设、配置、以及各类对象的天性等。本篇简介怎样行使和查询元数据,怎样更有效的治本SQLServer 数据库。

  对部分有经历的数据库开采和管理职员来说,元数据是格外有价值的。上边作者会介绍一下简短的原理,然后尽量用代码的点子一贯申明,终究“talk is cheap show me the code ”。

背景

在率先篇中本身介绍了如何访谈元数据,元数据为啥在数据Curry面,以及怎么样运用元数据。介绍了何等获悉各类数据库对象的在数据Curry面包车型大巴名字。第二篇,作者接纳了触发器的宗旨,因为它是三个能提供很好例子的数据库对象,而且在这么些目的中能够建议难题和化解难点。

本篇我将会介绍元数据中的索引,不止是因为它们本人很入眼,更关键的是它们是很好的元数据类型,举例列也许分布总括,那么些不是元数据中的对象。

目录对于别的关周到据库表都以必需的。然则,就好像吐司上的黄油同样,过度使用它们可能会在数据库中产生难题。不经常,能够对表实行过度索引或缺点和失误索引,也许营造重复索引。一时难题是选项三个坏的填充因子,错误地安装ignore_dup_key选项,创立一个永远不会被使用(但不能够不被保卫安全)的目录,遗失外键上的目录,也许将GUID作为主键的一片段。一言以蔽之,任何频仍利用的数据库系统中的索引都急需定期维护和表明,而目录视图是做到这么些干活儿的最直白的点子之一。

from :

那就是说什么样找到触发器的数量?

*  以sys.system_views索引元数据,Server数据库元数据。*is表起初。让咱们询问出数据库中采纳触发器的消息。能够告诉您近期SQL Server版本中有怎么着触发器。

SELECT schema_name(schema_ID) '.'  name

  FROM sys.system_views WHERE name LIKE '%trigger%'

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

sys.dm_exec_trigger_stats              

sys.server_trigger_events              

sys.server_triggers                    

sys.trigger_event_types                

sys.trigger_events                     

sys.triggers                           



(6 row(s) affected)

  在那之中sys.triggers看起来新闻相当多,它又包罗哪些列?上面这么些查询很容易查到:

 SELECT Thecol.name  ' '  Type_name(TheCol.system_type_id)

    CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

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

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

因而大家多这一个消息有了更好的敞亮,有了一个目录的目录。这么些定义有一点令人头晕,可是另一方面,它也是格外简单的。大家能够意识到元数据,再找个查询中,须求做的正是改造那个单词‘triggers’来探索你想要的视图名称。.

在二〇一二及其未来版本,能够使用二个新的表值函数不小地简化上述查询,并得避防止各个连接。在上边包车型地铁查询中,大家将搜索sys.triggers 视图 中的列。能够动用同一的查询通过退换字符串中的对象名称来获得其余视图的定义。

 SELECT name  ' '  system_type_name

    CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

查询结果如下:

 Column_Information

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

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

sys.dm_exec_describe_first_result_set函数的最大优势在于你能看出其他结果的列,不仅是表和视图、存款和储蓄过程如故贬值函数。

为了摸清任何列的消息,你能够选用稍微修改的版本,只供给退换代码中的字符串'sys.triggers'就可以,如下:

 Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM '   @TheParamater

SELECT

  name  ' '  system_type_name

    CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

  ORDER BY column_ordinal;

什么样是动态在线目录?

  每一个关系型数据库系统,譬如SQL Server 绝对要提供有关它的组织的音信,那么些信息往往需求经过sql语法来询问。平日那个新闻被封存在钦定数据表的结构中。那代表数据库中有三种区别的表:一是用户自定义的表和系统表可能视图(包涵元数据)。从SQL Server 2007起始,独有视图能够查询了,不可能直接观望数据表了。

 图片 1

系统视图

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

在数据库内部,有点系统表平素追踪数据库中发生的每一件事情。系统表存款和储蓄像表、活动、列、索引等作业。这么些完全符合EdgarCodd 的关系型数据库试试的十三条准则直译。这一个法则就是概念动态在线目录,它正是“关于数据的多少”,也叫作元数据。

 Edgar Codd  法规4, 描述如下:

‘The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.’

翻译:像普通数据一致,在逻辑层的数据表明了对数据库的陈述,以便于授权用户能使用一样的SQL语言来查询元数据,就犹如查询常规数量一致。

在SQL Server中,能够通过系统视图恐怕架构视图直接待上访谈动态在线目录,方便用户特别快捷的支付和治本数据库。

都有哪些索引能够查到?

让大家通过下边包车型客车归纳语句来看一下都有何索引在你的数据库上,代码如下:

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;

结果如下:

图片 2

干什么要去援用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.objects?

  在我们选择sys.triggers的新闻在此之前,需求来重新一回,全部的数据库对象都存在于sys.objects中,在SQL Server 中的对象包蕴以下:聚合的CL中华V函数,check 约束,SQL标量函数,CLOdyssey标量函数,CL奥迪Q5表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CL帕杰罗存款和储蓄进程,陈设指南,主键约束,老式法则,复制过滤程序,系统基础表,同义词,种类对象,服务队列,CLKoleosDML 触发器,SQL表值函数,表类型,用户自定义表,独一约束,视图和扩大存款和储蓄进程等。

  触发器是目的所以基础音信一定保存在sys.objects。不幸运的是,一时大家必要十一分的音信,这个新闻能够通过目录视图查询。那些额外数据有是什么吧?

 

  修改我们利用过的询问,来询问sys.triggers的列,本次我们会看到额外音信。那么些额外列是出自于sys.objects。

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

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

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

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

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

上述那些让大家领略在sys.triggers的附加新闻,可是因为它平素是表的子对象,所以有个别不相干新闻是不会议及展览示在那些钦赐的视图大概sys.triggers中的。今后就要带我们去继承找找这个消息。

哪些获得上述音信?

因为大家不可能直接待上访谈,需求利用视图和函数来看那几个新闻。只可以看看你权力内的多寡。有更加好的艺术在用户数据库中央银行使数据定义语言(DDL),这个DDL语句包含CREATE, DROP, ALTE奥德赛, GRANT, DENY, REVOKE 和sp_rename statements 等。总有一种格局能够运用DDL来修改视图中的任何新闻,即便并不总是明确的。

关系型数据库使用动态的种类视图中的数据描述数据库,不过最近还应该有未有标准。可是有二个富含在各个数据库内的架构能够读取这么些音讯:便是Information Schema

不幸运的是,那几个架构不足以提供丰裕音讯,这意味着大家须求运用SQL Server 系统数据库的视图和函数来补充消息。接下来要求解释一些术语和技术,作者会尽只怕少的内部情状足以让我们轻易地理解这几个示例

如图所示,怎么着访谈元数据,及其接口

 图片 3

 

* *

各类表中某个许个目录,并体现他们的名字

眼下的表并不特地有用,因为不能够一眼看出各样表有多少索引,以及它们是如何。下边这些讲话可以兑现:

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)

元数据 (metadata) 最遍及的定义为“有关数据的构造数据”,只怕再轻便一点就是“关于数据的音信”,日常生活中的图例、体育场合目录卡和名片等都可以看成是元数据。在关系型数据库管理系统 (DBMS) 中,元数据描述了数码的构造和含义。举个例子在处理、维护 SQL Server 大概是支付数据库应用程序的时候,大家日常要获得一些关联到数据库架构的消息:

触发器的难题

  触发器是行得通的,可是因为它们在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触发器关联,稍后大家将明白。

系统视图

搜寻未有集中索引的表

至于索引,您能够找到非常多妙不可言的事物。比如,这里有一种高效查找表的秘技,不要求使用集中索引(堆)

-- 展示所有没有聚集索引的表名称
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;

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

在数据库中列出触发器

那正是说怎么获取触发器列表?上面我在AdventureWorks数据库中实行查询,注意该库的视图中并未有触发器。

率先个查询全数音信都在sys.triggers 的目录视图中。

SELECT

  name AS TriggerName,

  coalesce(object_schema_name(parent_ID) '.'

     object_name(parent_ID),'Database (' db_name() ')') AS TheParent

FROM sys.triggers;



TriggerName                    TheParent

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

ddlDatabaseTriggerLog          Database (AdventureWorks2012)          

dEmployee                      HumanResources.Employee                

iuPerson                       Person.Person                          

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader         

iduSalesOrderDetail            Sales.SalesOrderDetail                 

uSalesOrderHeader              Sales.SalesOrderHeader                 

dVendor                        Purchasing.Vendor                      

iWorkOrder                     Production.WorkOrder                   

uWorkOrder                     Production.WorkOrder   

  作者利用元数据函数db_name()使SQL保持轻易。db_name()告诉自个儿数据库的名称。object_schema_name()用来查询object_ID表示的目的的架构,以及object_name**()**查询对象名称。那几个对目的的援引指向触发器的持有者,触发器能够是数据库自个儿,也能够是表:服务器触发器有和好的系统视图,稍后我会议及展览示。

假如想要看到有着触发器,那么大家最棒利用sys.objects 视图:

SELECT name as TriggerName, object_schema_name(parent_object_ID) '.'

     object_name(parent_object_ID) AS TheParent

            FROM   sys.objects

           WHERE  OBJECTPROPERTYEX(object_id,'IsTrigger') = 1

 

在意,输出不分包数据库级其余触发器,因为兼具的DML触发器都在sys.objects视图中,不过你会坎井之蛙在sys.triggers视图中的触发器。

地点查询结果:

name                           TheParent

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

dEmployee                      HumanResources.Employee

iuPerson                       Person.Person

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader

iduSalesOrderDetail            Sales.SalesOrderDetail

uSalesOrderHeader              Sales.SalesOrderHeader

dVendor                        Purchasing.Vendor

iWorkOrder                     Production.WorkOrder

uWorkOrder                     Production.WorkOrder

 

Information Schema

其一架构是一套视图,视图中是最近数据库的音讯。每七个数据库中都有其一架构,只可以见到日前数据库的对象音讯。能够平昔访问那一个架构的数额在第一的关系型数据中。在那之中架构视图不含有数据库安顿新闻。

对于不一致的关系型数据库之间的处管事人业那一个架构尤其入眼。它们特别适合日常专业,比如在拜见钱检查是还是不是留存,不过只要须求详细告诉则会蒙受限制。他们还利用一种稍有两样的标准命名法:举例,数据库被称为目录,用户定义的数据类型被称作“domain”。

事先看到MSDN上有人告诫说并不是采取INFORMATION_SCHEMA视图来承认对象架构,作者通晓是因为SQL Server允许在分化的架构中有雷同的表名字,由此当独有表名称的时候会有模糊。所以自身以为即便放心使用就好了。

 

各个索引中有个别许行在表里面?

由此连日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;

 

图片 4

然后,你能够修改那些代码,让其只是展现各个在索引表中的表有多少行。

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;

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

本身的表和视图有些许个触发器?

自个儿想知道各种表有多少个触发器,况兼什么情形下接触它们。上边大家列出了颇具触发器的表以及各样事件的触发器数量。每一个表只怕视图对于触发器行为都有贰个INSTEAD OF 触发器,恐怕是UPDATE, DELETE, 恐怕 INSERT

。不过三个表能够有多少个AFTE奔驰G级触发器行为。这个将展现在上边的查询中(排除视图):

SELECT

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

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

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

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

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

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

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

         GROUP BY parent_ID

          )TablesOnly;

--查询结果如下:

Table                            triggers    Delete Insert Update

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

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

假设超越一个触发器被触发在八个表上,它们不保证顺序,当然也可以行使sp_settriggerorder来调节顺序。通过使用objectpropertyex()元数据函数,要求依靠事件输入参数‘ExecIsLastDeleteTrigger’, ‘ExecIsLastInsertTrigger’ 或者‘ExecIsLastUpdateTrigger’来认同什么人是终极一个进行的触发器 。为了赢得第七个触发器,酌情采纳ObjectPropertyEx() 元数据函数,必要输入参数 ‘ExecIsFirstDeleteTrigger’, ‘ExecIsFirstInsertTrigger’ 可能 ‘ExecIsFirstUpdateTrigger’。

因此大家前日知晓了表有何样触发器,哪些事件触发这一个触发器。能够使用objectpropertyex()元数据函数,那个函数重临非常多比不上新闻,依据钦命的参数差异。通过查阅MSDN中的文书档案,查看里面包车型地铁叁个文书档案是还是不是有利于元数据查询,总是值得检查的。

包容性视图

包容性视图是保护元数据的视图,在SQL Server 二零零五事先是有系统表协助的,并且只向后相当。只在2007随后的版本帮衬对于一些系统表的查询,举例分区表等,独有部分元数据依旧性情是对用户可知的。对于包涵非常多用户、群组、剧中人物要么两千本子数据类型的数据库来讲,使用包容性视图是有秘密风险的,因为视图中一些列存款和储蓄了用户的ID也许项目ID,可能会返回NULL也许触发溢出。

表中都有多数索引吗?

例如您对一些表具备大批量索引感觉疑虑,那么能够选拔下边查询,该查询告诉您具备超越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;

 

◆某些表上定义的牢笼;

触发器哪一天触发事件?

让我们看一下那几个触发器,DML触发器可以在装有其他时间发出后触发,不过足以在封锁被拍卖前何况触发INSTEAD OF触发动作。下边我们就来看看全数的触及的到底是AFTERubicon 照旧INSTEAD OF 触发器,有事什么时直接触了触发器。

/* 列出触发器,无论它们是否启用,以及触发器事件。*/

SELECT

  convert(CHAR(25),name) AS triggerName,

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

     object_name(parent_ID),'Database (' db_name() ')')) AS TheParent,

       is_disabled,

       CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END

        Stuff (--get a list of events for each trigger

        (SELECT ', ' type_desc FROM sys.trigger_events te

           WHERE te.object_ID=sys.triggers.object_ID

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events

 FROM sys.triggers;

结果如下:

triggerName               TheParent                        is_disabled events

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

ddlDatabaseTriggerLog     Database (AdventureWorks2012)    1           AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_XML_INDEX, ALTER_FULLTEXT_INDEX, CREATE_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX, CREATE_SPATIAL_INDEX, CREATE_STATISTICS, UPDATE_STAT

t_AB                      dbo.AB                           0           INSTEAD OF INSERT

dEmployee                 HumanResources.Employee          0           INSTEAD OF DELETE

iuPerson                  Person.Person                    0           AFTER INSERT, UPDATE

iPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER INSERT

uPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER UPDATE

uPurchaseOrderHeader      Purchasing.PurchaseOrderHeader   0           AFTER UPDATE

iduSalesOrderDetail       Sales.SalesOrderDetail           0           AFTER INSERT, UPDATE, DELETE

uSalesOrderHeader         Sales.SalesOrderHeader           0           AFTER UPDATE

dVendor                   Purchasing.Vendor                0           INSTEAD OF DELETE

iWorkOrder                Production.WorkOrder             0           AFTER INSERT

uWorkOrder                Production.WorkOrder             0           AFTER UPDATE

 

As you will notice, we used a FOR XML PATH(‘’) trick here to make a list of the events for each trigger to make it easier to read. These events were pulled from the sys.trigger_events view using a correlated subquery.

细心到大家应用了FOR XML PATH(‘’)来列出事件的每一个触发器,更易于读取精晓。sys.trigger_events选择相关子查询来询问这个事件。

目录视图

目录视图提供了关于数据库架构的音讯。它们也被数据库引擎本身小编使用,特别在询问优化环节。因而这个视图须要更神速的不二诀要来得到元数据。除了复制、备份、数据库维护计划或SQL Server代理目录数据之外,全数元数据都通过这么些编目视图公开。

这个视图用一种异常特殊的措施排列,SQL Server对象的共有消息都保留在sys.objects里面。有广大派生视图,举例外键、约束、服务队列、表、视图和进度,那个视图用特定于被编目标靶子类型的新闻来补偿一般的对象音信

永不SQL Server元数据中的全部剧情都以指标。举个例子,二个列、索引或遍布计算音讯不是指标。一些如主键约束或扩张属性有多个匪夷所思的两面性,因为它们被被当做为三个目的,当被挟持键索引的实例化时,它就不是几个对象。有个别对象(首假如束缚)与另一种等级次序的目的具有父/子关系;父即表。

查询更新过的索引缺未有应用过有如何?

连日来有须要搜索自上次运维服务器来讲未曾行使的目录,极其是一旦服务器一贯在做琳琅满指标做事时。

--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,这里起到了手提式无线话机选取音讯的机能,之后大家会更详细的使用换这些目的来表明其作用。

◆有些表上定义的目录以及主键/外键的新闻。

触发器的多少长度?

诸大多据库职员不赞同冗长触发器的定义,但他俩可能会发觉,依照定义的长度排序的触发器列表是商讨数据库的一种有用艺术。

SELECT convert(CHAR(32),coalesce(object_schema_name(t.object_ID) '.','')

     name) AS TheTrigger,

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

     object_name(parent_ID),'Database (' db_name() ')')) AS theParent,

       len(definition) AS length --the length of the definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

ORDER BY length DESC;

访问sys.SQL_modules视图可以查阅触发器定义的SQL DDL,并按大小顺种类出它们,最上面是最大的。

结果:

TheTrigger                       theParent                        length

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

Sales.iduSalesOrderDetail        Sales.SalesOrderDetail           3666

Sales.uSalesOrderHeader          Sales.SalesOrderHeader           2907

Purchasing.uPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   2657

Purchasing.iPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   1967

Person.iuPerson                  Person.Person                    1498

ddlDatabaseTriggerLog            Database (AdventureWorks2012)    1235

Purchasing.dVendor               Purchasing.Vendor                1103

Production.uWorkOrder            Production.WorkOrder             1103

Purchasing.uPurchaseOrderHeader  Purchasing.PurchaseOrderHeader   1085

Production.iWorkOrder            Production.WorkOrder             1011

HumanResources.dEmployee         HumanResources.Employee          604

 

好呢,作者也许太指谪了,不太喜欢太长的,不过逻辑不常候会非常长。事实上,前三名以小编之见是不可相信的,尽管自个儿总是侧向于尽只怕少地应用触发器。

数据层应用程序视图

数据层应用程序视图被用来访问注册服务器新闻。特殊版本的服务器和音信用来检查那个本子是还是不是漂移。那是一种作为轻松的反省当前登记数据库版本的点子,直接用T-SQL查询。

这几个索引占用了稍稍空间?

设若策画知道索引占了多少空间,有无数‘胖’索引,正是满含了众多列,有非常大可能率索引中部分列不会冒出在任何查询中,那便是稀疏了空间。

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

 

图片 5

上边大家将介绍两种获得元数据的措施。

这一个触发器访谈了有一些对象

在代码中,每种触发器要会见多少对象(举例表和函数)?

我们只须求检查表达式重视项。这么些查询利用三个视图来列出“软”正视项(如触发器、视图和函数)。

SELECT coalesce(object_schema_name(parent_id)

           '.','') convert(CHAR(32),name) AS TheTrigger,

          count(*) AS Dependencies

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

GROUP BY name, parent_id

ORDER BY count(*) DESC;
--结果:

TheTrigger                               Dependencies

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

Sales.iduSalesOrderDetail                7

Sales.uSalesOrderHeader                  7

Purchasing.iPurchaseOrderDetail          5

Purchasing.uPurchaseOrderDetail          5

Purchasing.uPurchaseOrderHeader          3

Production.iWorkOrder                    3

Production.uWorkOrder                    3

dbo.t_AB                                 2

Purchasing.dVendor                       2

Person.iuPerson                          2

ddlDatabaseTriggerLog                    1

 

依然有四个触发器有7个依附!让大家就Sales.iduSalesOrderDetail来其实看一下,有怎样依赖。

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

DMV一般用来调优,检查判断难点和监察数据库服务器状态。最要害的功效正是提供了一种方式来查询数据库的选拔音讯。比如,不唯有查询到目录,何况能够查询到使用量的排序和耗费时间等。

总结表总的目录空间

让我们看看每一个表的总索引空间,以及表中的行数。

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

图片 6

收获元数据

一定触发器访谈依然写入哪些对象?

大家得以列出触发器在代码中援用的装有目的

SELECT

  convert(char(32),name) as TheTrigger,

  convert(char(32),coalesce([referenced_server_name] '.','')

             coalesce([referenced_database_name] '.','')

        coalesce([referenced_schema_name] '.','') [referenced_entity_name])
     as referencedObject

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

WHERE name LIKE 'iduSalesOrderDetail';

--查询结果:

TheTrigger                       referencedObject

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

iduSalesOrderDetail              Sales.Customer                 

iduSalesOrderDetail              Person.Person                  

iduSalesOrderDetail              Sales.SalesOrderDetail         

iduSalesOrderDetail              Sales.SalesOrderHeader          

iduSalesOrderDetail              Production.TransactionHistory  

iduSalesOrderDetail              dbo.uspLogError                

iduSalesOrderDetail              dbo.uspPrintError

 

元数据function

还会有相当多元数据函数,如object_name()或col_name(),它们提供有关当前数据库中的格局功用域对象的音讯。通过防止在元数听他们表达式中开始展览显式连接,它们提供了获取消息的走后门,因而,当与编目视图一齐行使时,它们得以扶助您更加快地赢得有关元数据的新闻。

怎么查询表使用索引的各样法子?

意识有关索引的少数质量,平常最棒利用质量函数作为快速格局。

-- 查询没有主键的表
SELECT  object_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 OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') ); 

大家从前的询问是科学的,扫描源码可见全部的注重项。大批量依赖项表名对于数据库的重构等急需特别小心,例如,修改叁个基础表的列。

据须要做什么样,您也许希望检查来自元数据视图的概念,实际不是接纳OBJECT_DEFINITION函数。

 SELECT definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');

目录存款和储蓄进程

有非常多囤积进度的严重性功用是为SQL Server的ODBC驱动程序提供元数据新闻。当你创立ODBC连接时,该新闻作为数据对象的成团。但是,那个消息一般是可用的,而且能够像其余另外存款和储蓄进程同样从SQL中动用。它们经常被以为不比目录视图有用,因为存款和储蓄进程再次回到的结果必须接纳INSERT插入多个表大概表变量中,供给接纳INSERT ... EXECUTE 语法。

何以元数据视图和功效很关键?

元数据视图和函数允许你寻找元数据,提供对数据库报告和总括,寻觅什么人有权力查看或转移什么数据,让您减掉重复输入,让大概具有隐敝在SQL Server Management Studio的音讯可查询,使铺排脚本更安全,更牢靠,寻找多年来的更换或创制,火速管理局地函数或进程,明确已登记数据库的版本,审计用于编码施行的数据库代码,发掘重复索引何况同意打折扣低效的点击操作。当与另外SQL Server工具(如默许追踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于支付和保管数据库是一对一火速的。

元数据视图和函数允许推行差不离不容许举办的操作,举例查找注重于内定的CL奥迪Q3用户定义类型或小名类型的参数。

那么些是目的,那个不是?

你或者注意到了部分想不到的事务。就算表的有的属性(如主键)自身正是目的,但列、总结或索引并不是对象。让大家弄掌握那点,因为它不是一心直观的反映在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;

 

图片 7.

你会意识索引不是指标。在第四个查询中,重临的object_ID是定义索引的表的ID。

此地的主题材料是关乎是错落有致的。约束能够分包多少个列,也足以由索引强制。索引能够涵盖几个列,可是各类很首要。总括数据还是可以包含多少个列,也得以与索引相关联。那意sys.indexes, sys.stats and sys.columns不从sys.objects承继。参数和品种也是那般。

获得元数据最常用的不二秘诀是使用 SQL Server 提供的种类存储进度与系统函数。

寻觅触发器的代码

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; 

 

结果如图:

图片 8

 

8个援引正在实施这些历程。大家在sys.SQL_modules中追寻了具备的定义可以找到三个一定的字符串,这种措施异常的慢很暴力,不过它是有效的!

自家是怎么样慢慢使用的?

学学应用元数据视图和函数的第一阶段是访谈从各个名牌的数据源(如SQL Server Central)中央银行使它们的查询。可以在MSDN上查询到。使用记录工具保存那一个查询。假设它是一个用于保存注释或部分的工具,能够让您在另各地方轻易地获得查询,那么它将会具备援助。一段时间后,就足以依靠使用须要对那一个查询稍作修改。然后,没有供给在object browser窗格中追寻表列表,您比比较快就足以从群集中获取适当的查询,实施它,并飞快获取新闻。

什么询问每八个表的每三个目录的每多少个列?

最简便易行的询问办法如下:

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;

 

 

图片 9

自然也足以钦命特定表,举例:

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

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

在颇具目的中找找字符串

自身想知道除了触发器之外是或不是还应该有其他对象调用那几个进程?大家有些修改查询以搜寻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; 

询问结果如下图:

图片 10

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

相比有效的查询实例

上面作者会议及展览示的例证都早就在二〇一〇和二〇一一多少个版本中测量试验。当然只用到了独家版本的末段多少个本子更新后的数据库。

下图中显得了独具继续sys.objects列的视图。那意味着它们除了有着那几个列以外,还会有列的应和项目。那是视图全体的消息譬喻create_date也都源于sys.objects

 图片 11

* *

要列出数据库中的全体视图(存款和储蓄进程和外键),只需实践以下操作 …

 

SELECT  object_schema_name(object_id) '.' name FROM sys.views;

 SELECT  object_schema_name(object_id) '.' name FROM sys.procedures;

SELECT name AS Foreign_key,object_schema_name(parent_object_ID) '.' object_name(parent_object_ID) AS parent,

object_schema_name(referenced_object_ID) '.' object_name(referenced_object_ID) AS referenced

FROM sys.foreign_keys;

 

对此全体别的的,您需求选用一个系统函数来过滤您想要的靶子。上面包车型大巴代码提供了有的一蹴而就的演示。因为大家只获得对象的称呼,所以利用sys.objects,它具备全部数据库对象共有的宗旨新闻的视图。借使我们供给一定于特定项目对象的消息,举个例子主键是不是持有系统生成的名称,那么你就务须为该特定项目标指标使用视图。

/* The Tables */
  --数据库中的所有用户表
    SELECT
      ob.name AS User_Table, Coalesce(ep.value, '') AS documentation
    FROM sys.objects AS ob
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = ob.object_id
           AND ep.class = 1
           AND ep.minor_id = 0
    WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1 

/* The Views */
  --视图
  SELECT ob.name AS ViewName, Coalesce(ep.value, '') AS documentation
  FROM sys.objects ob  LEFT OUTER JOIN sys.extended_properties AS ep
      ON ep.major_id = ob.object_id
         AND ep.class = 1
         AND ep.minor_id = 0
  WHERE objectproperty(ob.object_id,'IsView')= 1 


/* The Check Constraints */
 --Check约束
    SELECT
      objects.name AS Name_of_Check_Constraint,
      Object_Schema_Name(objects.parent_object_id)   '.'   Object_Name(objects.parent_object_id) AS parent,
   Coalesce(ep.value,'') AS documentation
   FROM sys.objects
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = objects.object_id AND ep.class=1
       AND ep.name='MS_Description'--microsoft 公约
    WHERE ObjectProperty(objects.object_id, 'IsCheckCnst') = 1 

/* The Constraints */ 

SELECT
--约束
  objects.name AS Name_of_Constraint, --see all constraints and parent table
  Lower(Replace(type_desc,'_',' ')),--the type of constraint
  Object_Schema_Name(objects.parent_object_id)   '.'   Object_Name(objects.parent_object_id) AS parent,
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' 
WHERE ObjectProperty(objects.object_id, 'IsConstraint') = 1;

/* The Defaults */
--默认
SELECT
  objects.name, 
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' 
WHERE ObjectProperty(objects.object_id, 'IsDefault') = 1;

/* The Default Constraints */
--数据库及其父表中的所有默认约束
SELECT objects.name AS Name_of_Default_Constraint,--see all Default constraints and parent table
   Coalesce(ep.value,'') AS documentation,
object_schema_name(objects.parent_object_id) '.' object_name(objects.parent_object_id) AS parent,
   Coalesce(EP_parent.value,'') AS documentation
FROM sys.objects 
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' --the microsoft convention
  LEFT OUTER JOIN sys.extended_properties AS EP_parent
    ON ep.major_id = objects.parent_object_id
       AND ep.name = 'MS_Description' --the microsoft convention   
 WHERE objectproperty(objects.object_id,'IsDefaultCnst')= 1;

/* The Executables */
--数据库中的所有可执行文件(过程、函数等)
SELECT
  oe.name AS Name_Of_Executable,
  Replace(Lower(oe.type_desc), '_', ' ') AS Type_Of_Executable,
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oe
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oe.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(oe.object_id, 'IsExecuted') = 1;


/* The Extended Stored Procedures */ 
--数据库中的所有扩展存储过程
SELECT
  oep.name AS Name_of_Extended_Procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oep
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oep.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(oep.object_id, 'IsExtendedProc') = 1;

/* The Inline Functions */ 
--数据库中的所有内联函数
SELECT ilf.name AS Inline_function,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ilf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ilf.object_id
       AND EP.name = 'MS_Description'
 WHERE objectproperty(ilf.object_id,'IsInlineFunction')= 1;

/* The Primary Keys */ 
--数据库中的所有主键及其父表
SELECT
  pk.name AS Primary_key,
  Object_Schema_Name(pk.parent_object_id)   '.'   Object_Name(pk.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS KeyDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS pk
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = pk.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = pk.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE ObjectProperty(pk.object_id, 'IsPrimaryKey') = 1;

/* The Stored Procedures */
--数据库中的所有存储过程
SELECT
  sp.name AS Stored_procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sp
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sp.object_id
       AND EP.minor_id = 0
       AND EP.name = 'MS_Description'  
WHERE ObjectProperty(sp.object_id, 'IsProcedure') = 1;

/* The Queues */ 
--数据库中的所有队列
SELECT
  q.name AS QueueName, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS q
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = q.object_id
       AND EP.name = 'MS_Description'   
WHERE ObjectProperty(q.object_id, 'IsQueue') = 1;

/* The Rules */ 
--数据库中的所有旧式规则
SELECT
  ru.name AS RuleName, --old-fashioned sybase-style rule
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ru
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ru.object_id
       AND EP.name = 'MS_Description'  
WHERE ObjectProperty(ru.object_id, 'IsRule') = 1;

/* The Scalar Functions */ 
--数据库中的所有标量函数。
SELECT
  sf.name AS Scalar_function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sf.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(sf.object_id, 'IsScalarFunction') = 1;

/* The System Tables */ 
--据库中的所有系统表
SELECT
  st.name AS System_table, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS st
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = st.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(st.object_id, 'IsSystemTable') = 1;

--数据库中的所有表,包括系统表
SELECT
  at.name AS TableName, 
  Lower(Replace(type_desc,'_',' ')),--约束的类型
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS at
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = at.object_id
       AND EP.name = 'MS_Description'
WHERE ObjectProperty(at.object_id, 'IsTable') = 1;

/* The TVFs*/ 
--数据库中的所有表值函数
SELECT
  tvf.name AS Table_Valued_Function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS tvf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tvf.object_id
       AND EP.name = 'MS_Description' --the microsoft convention 
WHERE ObjectProperty(tvf.object_id, 'IsTableFunction') = 1;

--数据库及其所有触发器。
SELECT
  tr.name AS TriggerName,
  Object_Schema_Name(tr.parent_object_id)   '.'   Object_Name(tr.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS TriggerDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS tr
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tr.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = tr.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE ObjectProperty(tr.object_id, 'IsTrigger') = 1;

/* The Unique Constraints */ 
--数据库及其父表中的所有惟一约束
SELECT uc.name AS Unique_constraint,--所有唯一的约束
object_schema_name(uc.parent_object_id) '.' object_name(uc.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS ConstraintDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS uc
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = uc.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = uc.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE objectproperty(uc.object_id,'IsUniqueCnst')= 1;

 

 

当然大家也得以调治这个语句来便于大家的高精度查找,举例:

--数据库中的全部视图在过去两周内被修改的有:

SELECT name AS ViewName, convert(char(11),modify_date,113)

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsView')= 1

AND modify_date > dateadd(week,-2, GetDate());

 

--上月开立的具备目的的名称和品种

SELECT coalesce(object_schema_name(obj.object_ID) '.','') obj.name AS ObjectName,

convert(varchar(30),lower(replace(type_desc,'_',' ')))

FROM sys.objects obj

WHERE create_date > dateadd(month,-1, GetDate());

--DBO框架结构中全数宗旨对象的称号和种类

SELECT coalesce(object_schema_name(obj.object_ID) '.','') obj.name AS ObjectName,

convert(varchar(30),lower(replace(type_desc,'_',' '))) as ObjectType

FROM sys.objects obj

WHERE parent_object_ID=0

AND schema_ID = schema_ID('dbo'); 

 

目录中有哪些列,顺序又是何等 ?

也足以聚集下面语句,每种索引汇总成一行,体现全数索引,具体代码如下:

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

功效如下:

图片 12

常用的与元数占有关的种类存储进程有以下一些:

列出服务器级触发器及其定义

大家能够经过系统视图掌握它们啊?嗯,是的。以下是列出服务器触发器及其定义的语句

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

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

总结

  如上,到这等级简单实用充分了。们曾经介绍了貌似的辩护,并介绍了搜寻数据库中的内容的主干方法。在下一篇中本人将会深切介绍触发器而且找到有效音讯的排序以福利可以因而系统视图从动态在线目录中募集的有用消息。

 

什么样查询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索引细节作为营造脚本。

图片 13

系统存款和储蓄进度

总结

  本文商量过触发器,而且你能识破触发器,以及潜在的标题。这里并不曾针对性有关触发器的询问提供叁个到家的工具箱,因为本身只是使用触发器作为示范来突显在询问系统视图时也许应用的一部分技艺。在我们学习了目录、列和参数之后,我们将回到触发器,并问询了编写访谈系统视图和information schema视图的询问的有个别常常用途。表是元数据的众多方面的基础。它们是三种类型的指标的父类,其余元数据如索引是表的性质。大家正在慢慢地拼命去开掘具有有关表的音信。期待下一期

元数据中还也有另外类别的目录吗?

再有三种相比特出的目录,一是空中引得,其新闻在sys.spatial_index_tessellations 和 sys.spatial_indexes表中。另八个是全文索引,其消息在fulltext_index_fragments,fulltext_index_catalog_usages, fulltext_index_columnsfulltext_indexes表中保留。**

◆sp_columns 再次来到钦命表或视图的列的详细新闻。

钻探索引总括新闻

今昔,让大家探讨一下分布总计数据或“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;

 

当它们与索引相关联时,总括数据承袭索引的称呼,并采纳与索引同样的列。

图片 14

◆sp_databases 重返当前服务器上的全数数据库的为主消息。

反省重复的总括新闻

由此比较与种种总括消息相关联的列号列表,您能够神速查看同一列或一组列是或不是有四个总计音讯。

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;

结果如下:

图片 15

展现了带有重复的总结对象,在本例中是sales.customer表在AccountNumber列上有三个近乎的总计对象。

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

总结

 在数据库中有为数十分的多有价值的音讯都在目录上。一旦表的数额变大,很轻易让表出现部分难点,比方无意中尚无聚焦索引或主键,也许有双重的目录或不须要的总括信息等。大家透过操纵怎么着询问那些索引的动态视图后能够高效查询定位使用表的音信,方便我们防御和解决那类难点,这么些基础艺术已经在DBA和数据库开荒的行事中变得尤其主要了,

◆sp_pkeys 重返钦定表的主键音信。

◆sp_server_info 重回当前服务器的各样特色及其相应取值。

编辑:mg 本文来源:索引元数据,Server数据库元数据

关键词: SQLSERVER