菜单

研讨SQL Server元数据(3):索引元数据

2019年4月3日 - MySQL

背景

在第2篇中自身介绍了哪些访问元数据,元数据为啥在数据Curry面,以及怎么着行使元数据。介绍了怎么样识破各类数据库对象的在数据Curry面包车型客车名字。第三篇,笔者选拔了触发器的大旨,因为它是一个能提供很好例子的数据库对象,并且在那么些目的中可见提议难点和平消除决难点。

本篇小编将会介绍元数据中的索引,不仅仅是因为它们本身很关键,更注重的是它们是很好的元数据类型,比如列也许分布总计,这个不是元数据中的对象。

目录对于其它关周全据库表都是必需的。不过,就像是吐司上的黄油1样,过度使用它们或许会在数据库中生出难题。有时,能够对表举行过度索引或缺点和失误索引,可能创设重复索引。有时难点是选用三个坏的填充因子,错误地安装ignore_dup_key选项,创制3个永恒不会被使用(但必须被爱护)的目录,丢失外键上的目录,也许将GUID作为主键的一片段。简单的说,任何频仍使用的数据库系统中的索引都亟待定期维护和认证,而目录视图是实现那个工作的最直接的艺术之壹。

简介

  在数据库中,大家除了存款和储蓄数据外,还蕴藏了汪洋的元数据。它们首要的功力就是讲述数据库怎么建立、配置、以及各类对象的性质等。本篇不难介绍怎么着使用和查询元数据,如何更使得的保管SQLServer
数据库。

  对部分有经验的数据库开发和管理人士而言,元数据是尤其有价值的。上边小编会介绍一下简易的规律,然后尽量用代码的办法直接证实,毕竟“talk
is cheap show me the code ”。

都有如何索引能够查到?

让大家由此下边包车型地铁简单语句来看一下都有怎么着索引在您的数据库上,代码如下:

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或壹的目录。要是该表有3个聚集索引,则有一行数据且index_id值为1;假设该表是1个堆(那只是表示该表未有聚集索引的另1种办法),则会有1行的index_id值为0。其它,无论该表是不是有聚集索引,各类非聚集索引都有1行,其index_id值大于壹。大家过滤了的目录,这么些索引是由数据库引擎优化顾问(DTA)创设的,指标无非是测试3个恐怕的目录是不是管用。以免它们积累起来,最佳把它们去掉。

若果你过1个多少个钦定的表,下面的这些查询是尤为合理的,需求在上边的事例中追加对象的钦点:

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

 

怎么是动态在线目录?

  每二个关系型数据库系统,比如SQL Server
一定要提供有关它的布局的新闻,那个信息往往要求通过sql语法来询问。平日那么些音讯被保留在内定数据表的组织中。那意味数据库中有两种分歧的表:一是用户自定义的表和系统表也许视图(包涵元数据)。从SQL
Server 200伍上马,唯有视图能够查询了,不能够平昔看到数据表了。

 图片 2

系统视图

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

在数据库内部,有部分系统表平素追踪数据库中发出的每一件事情。系统表存款和储蓄像表、活动、列、索引等业务。那一个完全符合埃德加Codd
的关系型数据库试试的十三条规则直译。那一个规则就是概念动态在线目录,它就是“关于数据的数据”,也叫作元数据。

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

翻译:像平常数据1致,在逻辑层的数量表达了对数据库的叙说,以便于授权用户能应用相同的SQL语言来查询元数据,就犹如查询常规数量一致。

在SQL
Server中,能够透过系统视图或许架构视图间接待上访问动态在线目录,方便用户特别急忙的花费和管理数据库。

每一个表中有多少个目录,并出示他们的名字

近期的表并不专门有用,因为不能够一眼看出各类表有多少索引,以及它们是怎么。上边那几个讲话能够兑现:

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)

什么获得上述新闻?

因为大家不可能一向访问,需求使用视图和函数来看那么些音信。只可以看到您权力内的数量。有更好的诀窍在用户数据库中利用数据定义语言(DDL),那些DDL语句包罗CREATE,
DROP, ALTETiguan, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种方法能够使用DDL来修改视图中的任何音信,就算并不总是肯定的。

关系型数据库使用动态的种类视图中的数据描述数据库,可是近来还有未有原则。但是有八个包罗在各类数据库内的架构可以读取那个新闻:正是Information
Schema

不走运的是,那一个架构不足以提供丰盛音讯,那意味大家须求动用SQL Server
系统数据库的视图和函数来补充消息。接下来供给说Bellamy(Bellamy)些术语和技艺,作者会尽大概少的细节足以让大家轻松地明白那一个示例

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

 图片 3

 

* *

搜索未有聚集索引的表

至于索引,您能够找到很多妙不可言的东西。例如,那里有一种高效查找表的主意,无需使用聚集索引(堆)

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

系统视图

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

通过三番五次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;

Information Schema

以此架构是1套视图,视图中是时下数据库的新闻。每一个数据库中都有这一个架构,只可以见到近来数据库的对象消息。能够一贯访问那些架构的数目在重点的关系型数据中。个中架构视图不分包数据库布署消息。

对此分歧的关系型数据库之间的处理工科作这一个架构特别首要。它们格外适合平时工作,例如在拜访钱检查是或不是留存,但是若是要求详细报告则会碰到限制。他们还运用1种稍有不相同的标准命名法:例如,数据库被叫作目录,用户定义的数据类型被称为“domain”。

事先看到MSDN上有人警告说毫不使用INFO大切诺基MATION_SCHEMA视图来确认对象架构,笔者理解是因为SQL
Server允许在不一样的框架结构中有相同的表名字,由此当只有表名称的时候会有模糊。所以小编以为即便放心使用就好了。

 

表中都有诸多索引吗?

假如你对一些表具有多量索引感到难以置信,那么能够应用上面查询,该查询告诉您具有超越陆个目录和索引计数超越列计数50%的表。它是一种任意采纳具有大批量目录的表的措施。

--超过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;

 

包容性视图

包容性视图是体贴元数据的视图,在SQL Server
200五事先是有系统表帮衬的,并且只向后特出。只在200五从此的版本支持对于一些系统表的询问,例如分区表等,唯有部分元数据大概性子是对用户可知的。对于富含很多用户、群组、剧中人物要么3000本子数据类型的数据库而言,使用包容性视图是有秘密风险的,因为视图中壹些列存款和储蓄了用户的ID也许项目ID,只怕会重临NULL恐怕触发溢出。

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

老是有要求找出自上次开发银行服务器来说未有行使的目录,尤其是如若服务器一向在做各样种种的劳作时。

--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,那里起到了手提式有线电话机应用消息的意义,之后我们会更详实的行使换这几个指标来验证其效果。

目录视图

目录视图提供了关于数据库架构的音信。它们也被数据库引擎自个儿笔者使用,尤其在查询优化环节。因而这么些视图要求更急迅的章程来获得元数据。除了复制、备份、数据库维护布署或SQL
Server代理目录数据之外,全体元数据都由此这一个编目视图公开。

这个视图用一种卓殊出格的点子排列,SQL
Server对象的共有消息都保存在sys.objects里面。有无数派生视图,比如外键、约束、服务队列、表、视图和经过,那一个视图用特定于被编目标对象类型的消息来填补1般的目的音信

毫不SQL
Server元数据中的全数内容皆以目的。例如,多个列、索引或分布总结信息不是目的。一些如主键约束或扩张属性有一个竟然的两面性,因为它们被被看做为一个指标,当被挟持键索引的实例化时,它就不是八个目的。有些对象(首倘诺约束)与另一种类型的目的拥有父/子关系;父即表。

那个索引占用了有点空间?

万一打算知道索引占了稍稍空间,有好多‘胖’索引,正是带有了广大列,有非常的大可能率索引中有的列不会现出在任何查询中,那正是荒废了半空中。

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

数据层应用程序视图

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

算算表总的目录空间

让大家看看各类表的总索引空间,以及表中的行数。

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

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

DMV一般用来调优,检查判断难题和监督检查数据库服务器状态。最要紧的功用正是提供了1种艺术来查询数据库的利用新闻。例如,不仅查询到目录,而且能够查询到使用量的排序和耗费时间等。

哪些查询表使用索引的各类法子?

发觉有关索引的一点质量,通常最佳应用性质函数作为赶快格局。

-- 查询没有主键的表
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;

元数据function

还有许多元数据函数,如object_name()或col_name(),它们提供有关当前数据库中的情势成效域对象的新闻。通过幸免在元数据表明式中开展显式连接,它们提供了获取音讯的近便的小路,由此,当与编目视图一起行使时,它们能够支持您更快地赢得有关元数据的音讯。

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

你或许注意到了部分想不到的事务。就算表的一对属性(如主键)本人就是目的,但列、计算或索引并非对象。让大家弄精晓那一点,因为它不是一点一滴直观的反映在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的ODBC驱动程序提供元数据新闻。当你建立ODBC连接时,该信息作为数据对象的聚集。可是,这一个音讯平日是可用的,并且可以像任何别的存款和储蓄进程壹样从SQL中采用。它们经常被认为不及目录视图有用,因为存款和储蓄进程再次回到的结果必须使用INSE猎豹CS6T插入一个表或许表变量中,须要使用INSE冠道T
… EXECUTE 语法。

怎么元数据视图和效应很首要?

元数据视图和函数允许你寻找元数据,提供对数据库报告和小结,找出什么人有权力查看或改变什么数据,让你收缩重复输入,让差不离全体隐藏在SQL
Server Management
Studio的消息可查询,使安顿脚本更安全,更保险,找出以来的变动或创办,快捷处理部分函数或进度,鲜明已注册数据库的本子,审计用于编码实践的数据库代码,发现重复索引并且同意减价扣低效的点击操作。当与其他SQL
Server工具(如暗中认可跟踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于开发和管理数据库是至极火速的。

元数据视图和函数允许实施大概不容许进行的操作,例如查找依赖于钦点的CLRubicon用户定义类型或别名类型的参数。

怎么着查询每一个表的每叁个目录的每八个列?

最简单易行的询问艺术如下:

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;

 

 

图片 8

自然也能够钦点特定表,例如:

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

自身是怎么样渐渐使用的?

上学使用元数据视图和函数的率先品级是收集从种种名牌的数据源(如SQL Server
Central)中运用它们的查询。能够在MSDN上询问到。使用记录工具保存这几个查询。假诺它是2个用来保存注释或一些的工具,能够让您在其余地方轻松地取得查询,那么它将聚会场全数协理。壹段时间后,就足以依照使用需求对这么些查询稍作修改。然后,不需求在object
browser窗格中摸索表列表,您相当的慢就能够从集合中赢得适当的查询,执行它,并飞快获取音讯。

目录中有何样列,顺序又是如何 ?

也能够集中上边语句,每一个索引汇总成一行,浮现全体索引,具体代码如下:

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;

成效如下:

图片 9

正如有效的询问实例

上边小编会议及展览示的例证都曾经在二〇一〇和二〇一一四个本子中测试。当然只用到了各自版本的终极八个本子更新后的数据库。

下图中显得了具备继续sys.objects列的视图。那代表它们除了有着那几个列以外,还有列的附和项目。那是视图全体的新闻比如create_date也都来源于sys.objects

 图片 10

* *

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

 

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

 

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

图片 11

总结

  如上,到那级别简单实用丰富了。们曾经介绍了貌似的抵触,并介绍了搜寻数据库中的内容的骨干方法。在下一篇中自个儿将会深切介绍触发器并且找到有效音讯的排序以方便能够透过系统视图从动态在线目录中募集的有用音信。

 

元数据中还有任何品类的目录吗?

再有两种比较奇特的目录,1是空间引得,其音讯在sys.spatial_index_tessellations

sys.spatial_indexes表中。另一个是全文索引,其音讯在fulltext_index_fragments,fulltext_index_catalog_usages, fulltext_index_columns
fulltext_indexes表中保留。**

商量索引总计新闻

现在,让我们探讨一下分布总括数据或“stats”。各个索引都有3个增大的统计对象,以便查询优化器能够提供2个恰如其分的查询布置。为此,它须求预计数据的“基数”,以明确为其余索引值重回多少行,并选拔那一个“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;

 

当它们与索引相关联时,总括数据继承索引的名目,并运用与索引相同的列。

图片 12

反省重复的总计消息

经过对比与各个计算信息相关联的列号列表,您能够火速查看同一列或壹组列是不是有多少个总计消息。

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;

结果如下:

图片 13

显示了带有重复的总计对象,在本例中是sales.customer表在AccountNumber列上有三个类似的计算对象。

总结

 在数据库中有不可胜道有价值的音讯都在目录上。壹旦表的数额变大,很简单让表出现有的题材,比如无意中尚无聚集索引或主键,恐怕有双重的目录或不须求的总计音讯等。大家通过驾驭怎么样查询那一个索引的动态视图后能够急迅查询定位使用表的新闻,方便我们预防和消除那类难题,这几个基础艺术已经在DBA和数据库开发的工作中变得特别主要了,

相关文章

发表评论

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

网站地图xml地图