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

发布时间:2019-04-16  栏目:sqlite  评论:0 Comments

背景

在首先篇中自作者介绍了什么样访问元数据,元数据为何在数据Curry面,以及如何选用元数据。介绍了何等得知各类数据库对象的在数据Curry面包车型地铁名字。第三篇,小编采取了触发器的宗旨,因为它是五个能提供很好例子的数据库对象,并且在那些目标中可见提议难题和消除难题。

本篇笔者将会介绍元数据中的索引,不仅仅是因为它们本人很关键,更主要的是它们是很好的元数据类型,比如列大概分布总括,那个不是元数据中的对象。

目录对于别的关全面据库表都以必需的。但是,就像是吐司上的黄油同样,过度使用它们或者会在数据库中爆发难点。有时,能够对表实行过度索引或缺点和失误索引,或许营造重复索引。有时难点是挑选一个坏的填写因子,错误地设置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中依然有2个条条框框。索引,但它指向的是堆,不意味索引。种种表在sys中都有一行。索引值为0或一的目录。假如该表有多个聚集索引,则有1行数据且index_id值为一;假若该表是叁个堆(这只是表示该表没有聚集索引的另1种办法),则会有壹行的index_id值为0。其余,无论该表是或不是有聚集索引,每一个非聚集索引都有1行,其index_id值大于一。大家过滤了的目录,这几个索引是由数据库引擎优化顾问(DTA)创制的,指标仅仅是测试三个大概的目录是不是有效。防止它们积累起来,最佳把它们去掉。

一经您过一个七个钦定的表,上边包车型大巴这一个查询是进一步客观的,须求在上边的事例中追加对象的内定:

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

 

怎么是动态在线目录?

  每3个关系型数据库系统,比如SQL Server
一定要提供有关它的构造的消息,那几个音讯往往要求经过sql语法来询问。常常那几个消息被保存在钦命数据表的结构中。那代表数据库中有二种不一致的表:一是用户自定义的表和系统表也许视图(包罗元数据)。从SQL
Server 200伍方始,只有视图能够查询了,不可能一贯看出数据表了。

 图片 2

系统视图

这种系统表或然视图的整合平常参考关系型数据库理论的文献叫做作为系统目录恐怕数额字典。

在数据库内部,有部分系统表一贯跟踪数据库中生出的每1件事情。系统表存款和储蓄像表、活动、列、索引等事务。这个完全符合EdgarCodd
的关系型数据库试试的103条规则直译。那么些规则正是概念动态在线目录,它正是“关于数据的数码”,也叫作元数据。

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

翻译:像平日数据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猎豹CS陆, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种办法能够行使DDL来修改视图中的任何音讯,固然并不一而再确定的。

关系型数据库使用动态的系统视图中的数据描述数据库,不过当前还有未有原则。不过有一个暗含在种种数据库内的架构能够读取那么些音信:就是Information
Schema

不幸运的是,这几个架构不足以提供丰裕音讯,那代表大家必要利用SQL Server
系统数据库的视图和函数来补偿信息。接下来供给说爱他美些术语和技能,笔者会尽恐怕少的细节足以让我们轻便地知道那几个示例

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

 图片 3

 

* *

搜寻未有聚集索引的表

关于索引,您能够找到繁多旧事物。例如,那里有1种高效查找表的章程,无需使用聚集索引(堆)

-- 展示所有没有聚集索引的表名称
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上有人告诫说并非接纳INFOHighlanderMATION_SCHEMA视图来承认对象架构,笔者晓得是因为SQL
Server允许在不一致的架构中有1致的表名字,由此当唯有表名称的时候会有模糊。所以我觉着尽管放心使用就好了。

 

表中都有广大索引吗?

倘诺您对某个表具备多量索引以为疑虑,那么能够应用上面查询,该查询告诉您具备超越陆个目录和索引计数抢先列计数二分之一的表。它是1种任意选择具有多量目录的表的点子。

--超过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⑤事先是有系统表帮衬的,并且只向后非常。只在2005将来的版本援助对于有些系统表的询问,例如分区表等,唯有部分元数据可能个性是对用户可知的。对于富含多数用户、群组、剧中人物仍然三千版本数据类型的数据库来讲,使用包容性视图是有私房危机的,因为视图中有的列存款和储蓄了用户的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.

 

专注:小编1度在代码里选择了动态管理视图sys.dm_db_index_usage_stats,那里起到了手提式有线电话机使用消息的功力,之后我们会更详实的使用换这一个指标来注解其效率。

目录视图

目录视图提供了有关数据库架构的音讯。它们也被数据库引擎本身小编使用,越发在询问优化环节。因而这么些视图供给更急迅的形式来博取元数据。除了复制、备份、数据库维护布置或SQL
Server代理目录数据之外,全体元数据都经过那么些编目视图公开。

那些视图用1种格外突出的格局排列,SQL
Server对象的共有音信都封存在sys.objects里面。有成都百货上千派生视图,比如外键、约束、服务队列、表、视图和进程,那么些视图用特定于被编指标靶子类型的新闻来填补1般的目的消息

毫无SQL
Server元数据中的全数剧情都是目的。例如,一个列、索引或分布计算音讯不是目的。①些如主键约束或扩充属性有1个出乎意外的两面性,因为它们被被看成为二个指标,当被恐吓键索引的实例化时,它就不是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

数据层应用程序视图

数据层应用程序视图被用于访问注册服务器消息。特殊版本的服务器和音信用来检查那几个本子是不是漂移。那是1种作为轻便的反省当前登记数据库版本的方法,直接用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)

DMV1般用来调优,检查判断难点和监督数据库服务器状态。最重视的功力就是提供了一种方法来查询数据库的应用新闻。例如,不仅查询到目录,而且能够查询到使用量的排序和耗费时间等。

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

发觉有关索引的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,非零的对象表,展现它们是子对象。

上边包车型大巴询问向您出示了1种查看这一个子对象并将其与父母关系的粗略方法。

--查询索引父对象(表名)和索引名称
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中动用。它们通常被感觉不比目录视图有用,因为存款和储蓄进度重返的结果必须利用INSERT插入2个表或然表变量中,须求利用INSERT
… EXECUTE 语法。

为什么元数据视图和功效很首要?

元数据视图和函数允许你寻觅元数据,提供对数据库报告和小结,寻觅什么人有权力查看或改换什么数据,让您收缩重复输入,让大致全部隐藏在SQL
Server Management
Studio的音信可查询,使安插脚本更安全,更可信,寻找多年来的更换或成立,急迅处理部分函数或进度,分明已注册数据库的版本,审计用于编码推行的数据库代码,发现重复索引并且同意优惠扣低效的点击操作。当与其它SQL
Server工具(如私下认可追踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于开辟和治本数据库是极度急忙的。

元数据视图和函数允许实行大概不容许实行的操作,例如查找依赖于钦赐的CLCRUISER用户定义类型或外号类型的参数。

哪些查询每3个表的每叁个索引的每八个列?

最轻巧易行的查询办法如下:

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上询问到。使用记录工具保存这么些查询。即使它是八个用于保存注释或局地的工具,能够让您在别的地点轻易地得到查询,那么它将聚会场全部援助。壹段时间后,就足以依照使用要求对那个查询稍作修改。然后,不供给在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;

 

对此具有其余的,您需求接纳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索引被视为索引的强大。我发现查看其细节的最佳办法是为它们营造二个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”。各个索引都有一个增大的总结对象,以便查询优化器能够提供1个适龄的查询布置。为此,它供给预计数据的“基数”,以分明为其余索引值重返多少行,并应用这个“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

反省重复的总结消息

经过相比与各种计算音讯相关联的列号列表,您可以长足查看同1列或壹组列是不是有五个计算音讯。

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