研究SQL Server元数据(一)

发布时间:2019-04-05  栏目:NoSQL  评论:0 Comments

背景

在首先篇中本身介绍了什么访问元数据,元数据为何在数据Curry面,以及哪些使用元数据。介绍了哪些识破各类数据库对象的在数据Curry面包车型的士名字。第2篇,小编选择了触发器的焦点,因为它是一个能提供很好例子的数据库对象,并且在那一个指标中可知提议难题和消除难题。

本篇小编将会介绍元数据中的索引,不仅仅是因为它们本身很要紧,更要紧的是它们是很好的元数据类型,比如列可能分布总括,这么些不是元数据中的对象。

目录对于其它关周详据库表都以必备的。可是,就如吐司上的黄油一样,过度使用它们恐怕会在数据库中产生难题。有时,可以对表举办过度索引或缺点和失误索引,可能创设重复索引。有时难题是选项贰个坏的填充因子,错误地安装ignore_dup_key选项,创制一个世代不会被运用(但必须被保证)的目录,丢失外键上的目录,也许将GUID作为主键的1有些。一言以蔽之,任何频仍利用的数据库系统中的索引都亟需定期维护和表明,而目录视图是水到渠成这一个干活儿的最直白的不2诀窍之壹。

简介

  在数据库中,我们除了存储数据外,还蕴藏了大气的元数据。它们首要的成效就是描述数据库怎么建立、配置、以及种种对象的属性等。本篇简单介绍怎样选用和查询元数据,怎样更管用的管理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或一的目录。假设该表有一个聚集索引,则有一行数据且index_id值为一;假如该表是一个堆(那只是表示该表未有聚集索引的另一种形式),则会有壹行的index_id值为0。其它,无论该表是还是不是有聚集索引,每种非聚集索引都有一行,其index_id值大于一。我们过滤了的目录,这几个索引是由数据库引擎优化顾问(DTA)创设的,目的唯有是测试贰个或许的目录是或不是可行。防止它们积累起来,最佳把它们去掉。

壹经你过3个多少个内定的表,上边包车型大巴这一个查询是越来越客观的,必要在地点的例证中加进对象的钦赐:

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

 

什么样是动态在线目录?

  每二个关系型数据库系统,比如SQL Server
一定要提供有关它的构造的音信,这么些音讯往往必要通过sql语法来询问。平常那些音讯被封存在钦赐数据表的结构中。那表示数据库中有二种区别的表:壹是用户自定义的表和系统表恐怕视图(包罗元数据)。从SQL
Server 200五开头,唯有视图能够查询了,无法直接阅览数据表了。

 图片 2

系统视图

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

在数据库内部,有一部分系统表一贯追踪数据库中爆发的每1件事情。系统表存储像表、活动、列、索引等事情。那么些完全符合EdgarCodd
的关系型数据库试试的拾3条轨道直译。那几个规则正是概念动态在线目录,它正是“关于数据的多少”,也叫作元数据。

 埃德加 Codd  准则四, 描述如下:

‘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
等。总有1种方法能够运用DDL来修改视图中的任何音讯,尽管并不总是肯定的。

关系型数据库使用动态的系统视图中的数据描述数据库,可是最近还有未有规则。可是有1个富含在各种数据库内的架构能够读取那个音信:就是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套视图,视图中是眼前数据库的消息。每四个数据库中都有其1架构,只雅观看日前数据库的靶子音信。能够一贯访问那个架构的数据在重中之重的关系型数据中。当中架构视图不分包数据库布置音讯。

对此差异的关系型数据库之间的处理工科作这一个架构特别主要。它们非凡适合平常工作,例如在造访钱检查是还是不是存在,但是一旦供给详细告诉则会惨遭限制。他们还运用一种稍有两样的标准命名法:例如,数据库被叫作目录,用户定义的数据类型被称为“domain”。

前边看来MSDN上有人告诫说绝不选用INFOSportageMATION_SCHEMA视图来确认对象架构,小编驾驭是因为SQL
Server允许在不一致的架构中有同样的表名字,因而当只有表名称的时候会有模糊。所以作者觉着就算放心使用就好了。

 

表中都有举不胜举索引吗?

一旦您对1些表具有大量索引感到疑虑,那么能够运用下边查询,该查询告诉您具有当先八个目录和索引计数抢先列计数5/10的表。它是壹种任意采用具有多量索引的表的点子。

--超过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
二〇〇六事先是有系统表援助的,并且只向后卓越。只在贰零零陆随后的版本协助对于一些系统表的询问,例如分区表等,唯有部分元数据或然天性是对用户可知的。对于富含很多用户、群组、角色要么三千本子数据类型的数据库而言,使用包容性视图是有地下风险的,因为视图中1些列存储了用户的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元数据中的全部内容都以目的。例如,1个列、索引或分布总计信息不是指标。1些如主键约束或增添属性有一个奇怪的两面性,因为它们被被用作为2个对象,当被挟持键索引的实例化时,它就不是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般用来调优,检查判断难点和监理数据库服务器状态。最重点的效用就是提供了一种办法来查询数据库的运用信息。例如,不仅查询到目录,而且能够查询到使用量的排序和耗费时间等。

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

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

-- 查询没有主键的表
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(),它们提供有关当前数据库中的格局效率域对象的音讯。通过制止在元数看新闻声明式中开展显式连接,它们提供了获取音讯的走后门,由此,当与编目视图壹起使用时,它们得以协助您更加快地获取有关元数据的音信。

那二个是指标,那3个不是?

您或者注意到了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.

你会意识索引不是目的。在第3个查询中,再次回到的object_ID是定义索引的表的ID。

那里的难题是涉及是错综复杂的。约束能够包涵多少个列,也得以由索引强制。索引能够分包多少个列,不过各类很重点。总括数据还足以包涵多少个列,也能够与索引相关联。那意sys.indexes,
sys.stats and
sys.columns不从sys.objects继承。参数和类型也是那样。

目录存款和储蓄进度

有广大存款和储蓄进度的重点职能是为SQL
Server的ODBC驱动程序提供元数据音讯。当您建立ODBC连接时,该新闻作为数据对象的集聚。然而,那么些音讯一般是可用的,并且可以像别的其它存储进度同样从SQL中使用。它们日常被认为比不上目录视图有用,因为存款和储蓄进度重临的结果必须运用INSE汉兰达T插入三个表可能表变量中,需求选拔INSERT
… EXECUTE 语法。

何以元数据视图和作用很关键?

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

元数据视图和函数允许实施大概不容许进行的操作,例如查找注重于指定的CL奇骏用户定义类型或别称类型的参数。

哪些查询每四个表的每三个索引的每贰个列?

最简便的询问艺术如下:

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上询问到。使用记录工具保存这一个查询。如果它是一个用以保存注释或1些的工具,能够让你在任哪个地点方轻松地获得查询,那么它将会具备援救。一段时间后,就能够依据使用需求对这一个查询稍作修改。然后,不须要在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

正如灵通的询问实例

下边我会议及展览示的例子都曾经在二〇一〇和2011七个本子中测试。当然只用到了各自版本的结尾3个本子更新后的数据库。

下图中显得了装有继续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索引被视为索引的扩大。小编发觉查看其细节的最棒方法是为它们创设1个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篇中本身将会浓厚介绍触发器并且找到有效新闻的排序以福利能够通过系统视图从动态在线目录中收集的有用消息。

 

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

还有二种相比奇特的目录,1是空间引得,其信息在sys.spatial_index_tessellations

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

探索索引总结音讯

今日,让我们探究一下分布总结数据或“stats”。每种索引都有二个叠加的计算对象,以便查询优化器能够提供3个适度的查询安排。为此,它须求猜测数据的“基数”,以显著为其余索引值再次来到多少行,并利用那个“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地图