菜单

研究SQL Server元数据(一)

2019年3月28日 - sqlite

背景

在率先篇中自己介绍了何等访问元数据,元数据为啥在数据Curry面,以及怎么样使用元数据。介绍了什么得知种种数据库对象的在数据Curry面包车型客车名字。第一篇,作者选取了触发器的大旨,因为它是二个能提供很好例子的数据库对象,并且在这些指标中可见建议难题和解决难题。

本篇作者将会介绍元数据中的索引,不仅仅是因为它们自个儿很要紧,更要紧的是它们是很好的元数据类型,比如列或然分布总计,这个不是元数据中的对象。

目录对于此外关全面据库表都是必不可少的。不过,就如吐司上的黄油一样,过度使用它们或许会在数据库中发生问题。有时,能够对表举行过度索引或缺失索引,只怕营造重复索引。有时难题是选项2个坏的填充因子,错误地安装ignore_dup_key选项,创设三个千古不会被利用(但必须被保险)的目录,丢失外键上的目录,大概将GUID作为主键的一有些。一言以蔽之,任何频仍使用的数据库系统中的索引都亟需定期维护和验证,而目录视图是到位那个工作的最直接的方法之一。

简介

  在数据库中,大家除了存款和储蓄数据外,还蕴藏了大气的元数据。它们重要的功能就是讲述数据库怎么建立、配置、以及各类对象的脾性等。本篇简单介绍咋样行使和查询元数据,怎么样更实惠的治本SQLServer
数据库。

  对一部分有经验的数据库开发和管理人员而言,元数据是11分有价值的。下边小编会介绍一下粗略的原理,然后尽量用代码的章程直接注脚,毕竟“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中还是有1个条款。索引,但它指向的是堆,不表示索引。各样表在sys中都有一行。索引值为0或1的目录。纵然该表有贰个聚集索引,则有一行数据且index_id值为1;要是该表是贰个堆(那只是意味着该表没有聚集索引的另一种方法),则会有一行的index_id值为0。别的,无论该表是或不是有聚集索引,每一种非聚集索引都有一行,其index_id值大于1。大家过滤了的目录,那么些索引是由数据库引擎优化顾问(DTA)创制的,指标只是是测试一个只怕的目录是不是行得通。避防它们积累起来,最佳把它们去掉。

如果你过贰个三个钦定的表,下边的这一个查询是更进一步客观的,必要在下面的例子中追加对象的钦定:

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

 

怎么着是动态在线目录?

  每多个关系型数据库系统,比如SQL Server
一定要提供有关它的布局的音讯,那一个新闻往往必要通过sql语法来询问。常常这几个新闻被保存在钦定数据表的协会中。那意味着数据库中有三种分化的表:一是用户自定义的表和系统表或然视图(包蕴元数据)。从SQL
Server 二零零五起初,唯有视图能够查询了,不能够一向看出数据表了。

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

翻译:像普通数据一致,在逻辑层的数据表明了对数据库的讲述,以便于授权用户能使用相同的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来修改视图中的任何消息,固然并不延续肯定的。

关系型数据库使用动态的系列视图中的数据描述数据库,可是当前还有没有标准化。可是有3个涵盖在各类数据库内的架构能够读取这一个消息:正是Information
Schema

不幸运的是,那么些框架结构不足以提供丰富音讯,那意味大家供给运用SQL Server
系统数据库的视图和函数来补充新闻。接下来须求解释一些术语和技能,作者会尽可能少的底细足以让大家轻松地知道这么些示例

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

 图片 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个数据库中都有那么些架构,只可以见到眼下数据库的对象消息。能够直接待上访问那些架构的数码在显要的关系型数据中。个中架构视图不带有数据库布署消息。

对此不相同的关系型数据库之间的拍卖工作那几个架构特别主要。它们极度适合平日工作,例如在造访钱检查是或不是存在,可是一旦需求详细告诉则会惨遭限制。他们还运用一种稍有两样的标准命名法:例如,数据库被叫作目录,用户定义的数据类型被称为“domain”。

事先看到MSDN上有人告诫说毫无使用INFO昂科威MATION_SCHEMA视图来认同对象框架结构,我掌握是因为SQL
Server允许在差别的架构中有同等的表名字,由此当唯有表名称的时候会有模糊。所以本人认为即使放心使用就好了。

 

表中都有无数索引吗?

若果您对某个表具有多量索引感到疑虑,那么可以使用下面查询,该查询告诉您具有超越6个目录和索引计数超越列计数四分之二的表。它是一种任意采取具有多量索引的表的主意。

--超过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
二零零五事先是有系统表支持的,并且只向后十一分。只在二零零六事后的本子帮助对于一些系统表的查询,例如分区表等,唯有部分元数据依旧脾气是对用户可知的。对于富含很多用户、群组、剧中人物依旧两千本子数据类型的数据库而言,使用包容性视图是有神秘危害的,因为视图中有的列存款和储蓄了用户的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元数据中的全数内容都是目的。例如,二个列、索引或分布计算消息不是目的。一些如主键约束或扩展属性有二个出人意表的两面性,因为它们被被看做为八个目的,当被威胁键索引的实例化时,它就不是二个对象。某个对象(首若是封锁)与另一种档次的对象拥有父/子关系;父即表。

这一个索引占用了多少空间?

比方打算知道索引占了有点空间,有不少‘胖’索引,正是带有了不少列,有恐怕索引中有个别列不会产出在别的查询中,那正是浪费了半空中。

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一般用来调优,诊断问题和监察和控制数据库服务器状态。最要紧的功效正是提供了一种办法来查询数据库的利用消息。例如,不仅查询到目录,而且能够查询到使用量的排序和耗费时间等。

哪些查询表使用索引的种种措施?

发觉有关索引的一点品质,平常最佳使用性质函数作为急速情势。

-- 查询没有主键的表
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翼虎T插入三个表也许表变量中,须要动用INSEPRADOT
… EXECUTE 语法。

干什么元数据视图和功能很主要?

元数据视图和函数允许你寻找元数据,提供对数据库报告和总计,找出哪个人有权力查看或变更什么数据,让你减弱重复输入,让大概全体隐藏在SQL
Server Management
Studio的音信可查询,使安插脚本更安全,更保证,找出以来的转移或创立,急迅处理部分函数或进度,分明已登记数据库的本子,审计用于编码实践的数据库代码,发现重复索引并且同意降价扣低效的点击操作。当与别的SQL
Server工具(如私下认可跟踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于开发和治本数据库是11分飞快的。

元数据视图和函数允许实施差不离极小概实施的操作,例如查找正视于钦赐的CL奥迪Q5用户定义类型或别称类型的参数。

何以查询每2个表的每1个目录的每二个列?

最简易的询问格局如下:

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窗格中摸索表列表,您异常快就足以从集合中获得适当的查询,执行它,并飞快获取音讯。

目录中有什么样列,顺序又是怎么 ?

也得以集中上边语句,每一种索引汇总成一行,体现全部索引,具体代码如下:

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

正如有效的询问实例

上面作者会议及展览示的例子都早就在2009和二〇一三多个本子中测试。当然只用到了分别版本的末尾1个本子更新后的数据库。

下图中展现了拥有继续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个系统函数来过滤您想要的对象。下边包车型客车代码提供了有些灵光的示范。因为大家只取得对象的称呼,所以利用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

总结

  如上,到那级别不难实用丰裕了。们已经介绍了相似的辩论,并介绍了搜索数据库中的内容的主导格局。在下一篇中自作者将会深深介绍触发器并且找到有效信息的排序以福利能够经过系统视图从动态在线目录中收载的有用新闻。

 

元数据中还有其余类别的目录吗?

还有二种比较出色的目录,一是空间引得,其新闻在sys.spatial_index_tessellations

sys.spatial_indexes表中。另1个是全文索引,其音信在fulltext_index_fragments,fulltext_index_catalog_usages, fulltext_index_columns
fulltext_indexes表中保存。**

深究索引总括音信

前天,让大家斟酌一下分布计算数据或“stats”。每种索引都有1个叠加的总结对象,以便查询优化器能够提供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

检查重复的总结信息

通过比较与每一种总括音信相关联的列号列表,您能够火速查看同一列或一组列是或不是有多少个总结音讯。

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