探究SQL Server元数据(一)

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

表中都有众多索引吗?

借使您对有些表具有大批量索引感到疑虑,那么可以行使上边查询,该查询告诉您具有超过6个目录和索引计数超越列计数四分之3的表。它是一种任意选用具有大量索引的表的不二等秘书籍。

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

 

数据层应用程序视图

数据层应用程序视图被用于访问注册服务器音讯。特殊版本的服务器和音信用来检查这几个本子是还是不是漂移。那是一种作为简单的检查当前注册数据库版本的措施,直接用T-SQL查询。

各类索引中有微微行在表里面?

透过接二连三sys.partitions视图,大家得以测算出索引中大致某些许行。笔者修改了1部分代码,关联了sys.extended_propertiesmanbet手机客户端3.0,,那样能够把备注的信息带出来。

--列出每个索引/堆的行数
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;

 

manbet手机客户端3.0 1

接下来,你能够修改这么些代码,让其只是展现每种在索引表中的表有多少行。

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;

什么是动态在线目录?

  每多个关系型数据库系统,比如SQL Server
一定要提供关于它的组织的音信,这一个消息往往须要经过sql语法来查询。经常这几个新闻被封存在钦赐数据表的构造中。那象征数据库中有二种分歧的表:一是用户自定义的表和系统表也许视图(包蕴元数据)。从SQL
Server 200五起来,唯有视图能够查询了,不能够直接观察数据表了。

 manbet手机客户端3.0 2

系统视图

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

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

 埃德加 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 
  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;

 

manbet手机客户端3.0 3

简介

  在数据库中,大家除了存款和储蓄数据外,还蕴藏了汪洋的元数据。它们主要的作用正是描述数据库怎么建立、配置、以及各样对象的性质等。本篇简单介绍怎么样利用和查询元数据,怎样更实用的军管SQLServer
数据库。

  对某些有经历的数据库开发和管理职员而言,元数据是那些有价值的。上面笔者会介绍一下简练的规律,然后尽量用代码的不二等秘书籍一直表明,究竟“talk
is cheap show me the code ”。

什么样询问每三个表的每三个目录的每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;

 

 

manbet手机客户端3.0 4

自然也足以内定特定表,例如:

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

目录存款和储蓄进度

有许多囤积进程的第二职能是为SQL
Server的ODBC驱动程序提供元数据新闻。当你建立ODBC连接时,该音讯作为数据对象的联谊。不过,那个消息平时是可用的,并且可以像任何其余存款和储蓄进度一样从SQL中利用。它们平日被认为不比目录视图有用,因为存款和储蓄进程再次来到的结果必须选用INSEWranglerT插入二个表可能表变量中,必要运用INSE劲客T
… EXECUTE 语法。

为何元数据视图和效用很关键?

元数据视图和函数允许你寻找元数据,提供对数据库报告和小结,找出何人有权力查看或改动什么数据,让您减掉重复输入,让大致全部隐藏在SQL
Server Management
Studio的音讯可查询,使计划脚本更安全,更牢靠,找出以来的改观或创办,急迅处理部分函数或进度,分明已注册数据库的本子,审计用于编码实践的数据库代码,发现重复索引并且同意降价扣低效的点击操作。当与其余SQL
Server工具(如默许跟踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于开发和治本数据库是一对一飞快的。

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

询问更新过的索引缺未有动用过有哪些?

接连有至关重要找出自上次起动服务器来说从未利用的目录,特别是只要服务器一贯在做各个各个的工作时。

--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,那里起到了手提式有线电话机使用新闻的功用,之后我们会更详尽的选取换这么些目的来注脚其功能。

哪些获取上述音信?

因为大家无法一贯访问,要求运用视图和函数来看那一个新闻。只可以看到你权力内的多少。有越来越好的点子在用户数据库中动用数据定义语言(DDL),这个DDL语句包括CREATE,
DROP, ALTE锐界, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种方法能够运用DDL来修改视图中的任何消息,尽管并不接二连三肯定的。

关系型数据库使用动态的系统视图中的数据描述数据库,不过当前还有未有标准。然则有一个含有在各类数据库内的架构可以读取那一个音信:正是Information
Schema

不幸运的是,那几个框架结构不足以提供丰富音讯,那象征大家供给选拔SQL Server
系统数据库的视图和函数来填补音信。接下来需求解释一些术语和技术,作者会尽恐怕少的底细足以让我们轻松地通晓那一个示例

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

 manbet手机客户端3.0 5

 

* *

都有怎么样索引能够查到?

让大家透过上面包车型地铁简便语句来看一下都有怎样索引在您的数据库上,代码如下:

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;

结果如下:

manbet手机客户端3.0 6

为什么要去引用sys.tables?那是因为它是保险只获得用户表的最简便方法。大家选用index_id
的values大于0,因为1旦不为表创立集群索引,在sys中照旧有四个条条框框。索引,但它指向的是堆,不意味索引。各样表在sys中都有壹行。索引值为0或1的目录。如若该表有多个聚集索引,则有壹行数据且index_id值为一;即使该表是1个堆(这只是意味该表未有聚集索引的另一种方法),则会有一行的index_id值为0。其它,无论该表是或不是有聚集索引,每种非聚集索引都有1行,其index_id值大于壹。大家过滤了的目录,这个索引是由数据库引擎优化顾问(DTA)创造的,指标只是是测试三个也许的目录是还是不是行得通。以免它们积累起来,最佳把它们去掉。

设若您过1个四个内定的表,上边包车型客车那个查询是尤为客观的,供给在地点的例证中扩充对象的钦定:

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

 

Information Schema

以此架构是一套视图,视图中是时下数据库的音信。每三个数据库中都有那些架构,只能见到眼下数据库的目的消息。能够平昔访问那些架构的数目在根本的关系型数据中。当中框架结构视图不分包数据库安排音讯。

对于不一样的关系型数据库之间的处理工科作那么些架构尤其重点。它们格外适合经常工作,例如在拜访钱检查是或不是留存,不过即使要求详细告诉则会碰到限制。他们还利用一种稍有两样的标准命名法:例如,数据库被喻为目录,用户定义的数据类型被喻为“domain”。

事先看到MSDN上有人警告说不用采取INFOOdysseyMATION_SCHEMA视图来承认对象架构,作者知道是因为SQL
Server允许在差异的架构中有一样的表名字,因而当唯有表名称的时候会有模糊。所以本身觉得就算放心使用就好了。

 

总结

 在数据库中有诸多有价值的音信都在目录上。1旦表的多寡变大,很简单让表出现局地难点,比如无意中绝非聚集索引或主键,恐怕有再度的目录或不要求的总括消息等。大家经过操纵哪些询问这么些索引的动态视图后能够不慢查询定位使用表的音信,方便大家预防和平化解决这类难题,那一个基础措施已经在DBA和数据库开发的干活中变得进一步重要了,

目录视图

目录视图提供了有关数据库架构的消息。它们也被数据库引擎本人自个儿使用,尤其在查询优化环节。因而这一个视图须求更飞速的办法来获得元数据。除了复制、备份、数据库维护安排或SQL
Server代理目录数据之外,全部元数据都通过那一个编目视图公开。

那几个视图用一种很是出格的方式排列,SQL
Server对象的共有音信都封存在sys.objects里面。有不少派生视图,比如外键、约束、服务队列、表、视图和经过,那么些视图用特定于被编目标对象类型的音信来补充一般的靶子新闻

并非SQL
Server元数据中的全体内容皆以目的。例如,三个列、索引或分布总计新闻不是目的。壹些如主键约束或扩展属性有三个想不到的两面性,因为它们被被视作为3个目的,当被强制键索引的实例化时,它就不是3个对象。有个别对象(首要是封锁)与另1种档次的对象拥有父/子关系;父即表。

那3个是目的,这个不是?

你大概注意到了有个别竟然的业务。就算表的局部品质(如主键)本人便是指标,但列、总括或索引并非对象。让我们弄通晓那或多或少,因为它不是完全直观的反映在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;

 

manbet手机客户端3.0 7.

您会意识索引不是目的。在首先个查询中,重返的object_ID是定义索引的表的ID。

那边的题材是涉及是错综复杂的。约束能够分包多少个列,也得以由索引强制。索引能够涵盖多少个列,不过种种很关键。总结数据还足以包涵多少个列,也得以与索引相关联。那意sys.indexes,
sys.stats and
sys.columns不从sys.objects继承。参数和档次也是这么。

自家是何等逐步使用的?

上学应用元数据视图和函数的第3阶段是采访从种种名牌的数据源(如SQL Server
Central)中运用它们的询问。能够在MSDN上询问到。使用记录工具保存这么些查询。要是它是多少个用于保存注释或部分的工具,能够让你在另各地方轻松地获取查询,那么它将聚会场全部帮助。1段时间后,就能够依照使用必要对这一个查询稍作修改。然后,不必要在object
browser窗格中找寻表列表,您非常快就能够从集合中收获适当的询问,执行它,并神速获取新闻。

反省重复的总计新闻

通过对比与各样总计消息相关联的列号列表,您能够飞快查看同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;

结果如下:

manbet手机客户端3.0 8

展现了涵盖重复的总计对象,在本例中是sales.customer表在AccountNumber列上有八个类似的总结对象。

相比较有效的查询实例

下边作者会议及展览示的例子都已经在二零零六和二〇一三三个版本中测试。当然只用到了个别版本的最后二个本子更新后的数据库。

下图中展现了富有继续sys.objects列的视图。那代表它们除了有着这一个列以外,还有列的附和档次。那是视图全数的音讯比如create_date也都出自sys.objects

 manbet手机客户端3.0 9

* *

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

 

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

 

怎么着查询表使用索引的各样方法?

察觉有关索引的一点质量,经常最棒使用品质函数作为连忙方式。

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

总结

  如上,到那级别简单实用足够了。们曾经介绍了貌似的理论,并介绍了探寻数据库中的内容的主干办法。在下1篇中小编将会深切介绍触发器并且找到有效新闻的排序以方便可以通过系统视图从动态在线目录中搜集的有用消息。

 

总结表总的目录空间

让大家看看每一种表的总索引空间,以及表中的行数。

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;

manbet手机客户端3.0 10

系统视图

背景

在首先篇中自个儿介绍了如何访问元数据,元数据为何在数据库里面,以及哪些利用元数据。介绍了什么样获悉种种数据库对象的在数据Curry面包车型地铁名字。第一篇,作者选择了触发器的宗旨,因为它是3个能提供很好例子的数据库对象,并且在这些指标中能够提议难点和缓解难点。

本篇作者将会介绍元数据中的索引,不仅仅是因为它们本人很重点,更关键的是它们是很好的元数据类型,比如列或许分布计算,这一个不是元数据中的对象。

目录对于任何关全面据库表都以供给的。不过,就好像吐司上的黄油1样,过度施用它们大概会在数据库中爆发难点。有时,能够对表举办过度索引或缺失索引,或许营造重复索引。有时难题是接纳二个坏的填写因子,错误地安装ignore_dup_key选项,创制1个永远不会被运用(但无法不被保证)的目录,丢失外键上的目录,可能将GUID作为主键的壹有的。简单来说,任何频仍利用的数据库系统中的索引都要求定期维护和注解,而目录视图是达成那几个干活儿的最直白的主意之1。

包容性视图

包容性视图是保证元数据的视图,在SQL Server
2007以前是有系统表援救的,并且只向后分外。只在2006后头的版本帮助对于1些系统表的询问,例如分区表等,只有部分元数据也许天性是对用户可知的。对于富含很多用户、群组、剧中人物要么两千本子数据类型的数据库而言,使用包容性视图是有私人住房风险的,因为视图中有个别列存款和储蓄了用户的ID只怕项目ID,大概会回去NULL或然触发溢出。

何以查询XML索引?

XML索引被视为索引的恢宏。笔者发觉查看其细节的最棒点子是为它们营造3个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索引细节作为营造脚本。

manbet手机客户端3.0 11

动态管理视图和成效(DMVs)

DMV1般用来调优,会诊难点和监察数据库服务器状态。最根本的意义便是提供了一种艺术来查询数据库的行使消息。例如,不仅查询到目录,而且能够查询到使用量的排序和耗费时间等。

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

也足以集中下面语句,每一个索引汇总成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;

职能如下:

manbet手机客户端3.0 12

元数据function

再有为数不少元数据函数,如object_name()或col_name(),它们提供关于当前数据库中的形式成效域对象的音信。通过制止在元数据表明式中展开显式连接,它们提供了获取新闻的近便的小路,由此,当与编目视图1起利用时,它们得以支持你更加快地得到有关元数据的消息。

每一个表中有稍许个目录,并展现他们的名字

眼下的表并不越发有用,因为无法一眼看出各种表有多少索引,以及它们是什么。上边那一个讲话能够落成:

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)

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

再有两种相比奇特的目录,一是空中引得,其新闻在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;

 

当它们与索引相关联时,总结数据继承索引的称谓,并应用与索引相同的列。

manbet手机客户端3.0 13

找寻没有聚集索引的表

有关索引,您能够找到很多幽默的东西。例如,这里有一种高效查找表的法门,无需使用聚集索引(堆)

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

留下评论

网站地图xml地图