商讨SQL Server元数据(叁):索引元数据

发布时间:2019-04-08  栏目:MySQL  评论:0 Comments

背景

在率先篇中本身介绍了怎么访问元数据,元数据为何在数据Curry面,以及怎么样行使元数据。介绍了什么得知各样数据库对象的在数据Curry面包车型客车名字。第3篇,小编选用了触发器的核心,因为它是二个能提供很好例子的数据库对象,并且在这一个指标中可见提议难题和缓解难点。

本篇笔者将会介绍元数据中的索引,不仅仅是因为它们自身很关键,更珍视的是它们是很好的元数据类型,比如列或许分布总结,这个不是元数据中的对象。

目录对于此外关周到据库表都以不可缺少的。不过,就好像吐司上的黄油壹样,过度施用它们恐怕会在数据库中生出难题。有时,能够对表举办过度索引或缺点和失误索引,也许构建重复索引。有时难点是选取二个坏的填写因子,错误地安装ignore_dup_key选项,成立贰个永久不会被利用(但无法不被保护)的目录,丢失外键上的目录,也许将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中都有1行。索引值为0或一的目录。要是该表有三个聚集索引,则有壹行数据且index_id值为一;假使该表是一个堆(那只是代表该表未有聚集索引的另1种形式),则会有一行的index_id值为0。其它,无论该表是或不是有聚集索引,各类非聚集索引都有一行,其index_id值大于一。大家过滤了的目录,这一个索引是由数据库引擎优化顾问(DTA)创设的,目标只是是测试四个只怕的目录是不是行得通。以免它们积累起来,最棒把它们去掉。

如果您过3个多少个钦命的表,上边包车型地铁这一个查询是更为合理的,需求在地点的例证中加进对象的钦命:

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

 

什么是动态在线目录?

  每二个关系型数据库系统,比如SQL Server
一定要提供关于它的协会的新闻,那一个消息往往须要经过sql语法来询问。日常这么些音讯被封存在内定数据表的构造中。这代表数据库中有二种分歧的表:一是用户自定义的表和系统表只怕视图(包涵元数据)。从SQL
Server 2005初叶,唯有视图能够查询了,不能够直接观察数据表了。

 图片 2

系统视图

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

在数据库内部,有一对系统表一直追踪数据库中发生的每一件事情。系统表存款和储蓄像表、活动、列、索引等工作。那一个完全符合埃德加Codd
的关系型数据库试试的拾3条轨道直译。那几个规则就是概念动态在线目录,它正是“关于数据的多寡”,也叫作元数据。

 埃德加 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, ALTE纳瓦拉, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种方法能够运用DDL来修改视图中的任何音讯,尽管并不接二连三肯定的。

关系型数据库使用动态的系统视图中的数据描述数据库,不过当前还有未有原则。不过有三个包括在各种数据库内的架构可以读取那个新闻:正是Information
Schema

不幸运的是,这些架构不足以提供充足消息,那意味大家须求动用SQL Server
系统数据库的视图和函数来补偿音讯。接下来须要说惠氏(WYETH)些术语和技术,笔者会尽恐怕少的细节足以让我们轻松地知道那一个示例

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

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

那一个框架结构是一套视图,视图中是近年来数据库的消息。每3个数据库中都有这么些架构,只可以看到最近数据库的目的音信。能够一向访问这么些架构的数额在关键的关系型数据中。当中架构视图不带有数据库陈设消息。

对于差异的关系型数据库之间的处理工作这些框架结构越发关键。它们万分适合平常工作,例如在造访钱检查是还是不是留存,可是若是急需详细告知则会见临限制。他们还接纳一种稍有例外的标准命名法:例如,数据库被称之为目录,用户定义的数据类型被称之为“domain”。

事先看到MSDN上有人警告说绝不选拔INFOXC90MATION_SCHEMA视图来确认对象架构,作者精晓是因为SQL
Server允许在不一致的架构中有同等的表名字,由此当唯有表名称的时候会有模糊。所以小编觉着就算放心使用就好了。

 

表中都有广大索引吗?

假定你对一些表具有大量索引感到难以置信,那么能够使用上面查询,该查询告诉您具有超越伍个目录和索引计数超过列计数百分之五十的表。它是一种任意选拔具有大批量目录的表的格局。

--超过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伍事后的本子协助对于某个系统表的查询,例如分区表等,唯有部分元数据如故特性是对用户可知的。对于富含很多用户、群组、剧中人物依旧三千版本数据类型的数据库而言,使用包容性视图是有地下风险的,因为视图中有的列存款和储蓄了用户的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里面。有为数不少派生视图,比如外键、约束、服务队列、表、视图和经过,这一个视图用特定于被编指标对象类型的音讯来补充一般的目的音信

无须SQL
Server元数据中的全部内容都是目的。例如,3个列、索引或分布总结音信不是目的。一些如主键约束或扩充属性有一个意想不到的两面性,因为它们被被看做为三个目的,当被恐吓键索引的实例化时,它就不是叁个对象。有个别对象(首尽管束缚)与另1种档次的靶子拥有父/子关系;父即表。

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

设若打算知道索引占了有个别空间,有不少‘胖’索引,正是带有了不少列,有相当大概率索引中有个别列不会师世在别的查询中,那正是浪费了半空中。

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(),它们提供有关当前数据库中的情势作用域对象的音信。通过制止在元数听盛名式中开始展览显式连接,它们提供了获撤消息的近便的小路,因而,当与编目视图1起使用时,它们能够扶持您越来越快地得到有关元数据的音讯。

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

你也许注意到了一部分意外的工作。即使表的1部分性格(如主键)自己正是指标,但列、总括或索引并非对象。让大家弄领会那或多或少,因为它不是全然直观的浮将来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连接时,该音信作为数据对象的成团。不过,这一个音信通常是可用的,并且能够像其余其余存储进度1样从SQL中采纳。它们经常被认为不及目录视图有用,因为存款和储蓄进程再次回到的结果必须运用INSEXC90T插入一个表大概表变量中,必要动用INSELANDT
… EXECUTE 语法。

干什么元数据视图和效果很重点?

元数据视图和函数允许你寻找元数据,提供对数据库报告和总括,找出什么人有权力查看或转移什么数据,让你缩短重复输入,让差不离全数隐藏在SQL
Server Management
Studio的音讯可查询,使安排脚本更安全,更保证,找出以来的变动或创设,火速处理部分函数或进程,分明已登记数据库的本子,审计用于编码实践的数据库代码,发现重复索引并且同意减价扣低效的点击操作。当与其余SQL
Server工具(如暗许跟踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于开发和管理数据库是一定急速的。

元数据视图和函数允许实施大致不只怕实施的操作,例如查找依赖于钦定的CLRubicon用户定义类型或小名类型的参数。

什么询问每1个表的每1个目录的每2个列?

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

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窗格中寻找表列表,您极快就足以从集合中拿走适当的查询,执行它,并急速获取音讯。

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

也能够集中上面语句,每一种索引汇总成1行,呈现全数索引,具体代码如下:

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;

 

对于拥有别的的,您须要利用3个体系函数来过滤您想要的靶子。上面包车型客车代码提供了1部分卓有效能的以身作则。因为我们只得到对象的称呼,所以选取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索引被视为索引的壮大。作者发现查看其细节的最佳法子是为它们创设2个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”。每一种索引都有一个外加的总括对象,以便查询优化器能够提供三个适合的询问计划。为此,它须要推断数据的“基数”,以显明为任何索引值再次来到多少行,并采取那些“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列上有八个八玖不离拾的计算对象。

总结

 在数据库中有广大有价值的新闻都在目录上。1旦表的数目变大,很不难让表出现局部标题,比如无意中从未聚集索引或主键,也许有重复的目录或不要求的总计音信等。大家经过操纵哪些询问这个索引的动态视图后能够一点也不慢查询定位使用表的音讯,方便大家预防和平化解决那类难点,这几个基础措施已经在DBA和数据库开发的干活中变得更其主要了,

留下评论

网站地图xml地图