深究SQL Server元数据(一)

发布时间:2019-04-10  栏目:MyBatis  评论:0 Comments

查询更新过的索引缺未有行使过有何?

接连有不可缺少找出自上次起动服务器来说从未利用的目录,越发是只要服务器一向在做各个各个的行事时。

--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,那里起到了手机应用新闻的效益,之后大家会更详实的运用换这几个指标来验证其效果。

系统视图

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

你可能注意到了壹部分意料之外的政工。即使表的部分性质(如主键)本身正是目的,但列、总括或索引并非对象。让大家弄驾驭那一点,因为它不是截然直观的反映在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;

 

图片 1.

你会发觉索引不是指标。在率先个查询中,重回的object_ID是定义索引的表的ID。

此处的题目是关系是复杂的。约束能够包含多少个列,也能够由索引强制。索引能够分包多少个列,可是各类很重点。总计数据还足以包蕴几个列,也能够与索引相关联。那意sys.indexes,
sys.stats and
sys.columns不从sys.objects继承。参数和档次也是那般。

Information Schema

本条架构是壹套视图,视图中是当下数据库的音信。每一个数据库中都有其一框架结构,只可以看看眼下数据库的对象音信。能够直接待上访问这么些架构的数量在主要的关系型数据中。在那之中架构视图不包括数据库计划音信。

对此分裂的关系型数据库之间的拍卖工作那些架构尤其关键。它们分外适合平时工作,例如在走访钱检查是否存在,但是只要急需详细告知则会境遇限制。他们还采纳一种稍有差异的标准命名法:例如,数据库被叫作目录,用户定义的数据类型被称为“domain”。

事先看到MSDN上有人告诫说毫无使用INFOXC90MATION_SCHEMA视图来承认对象框架结构,小编领悟是因为SQL
Server允许在不相同的架构中有雷同的表名字,由此当唯有表名称的时候会有模糊。所以本身觉着就算放心使用就好了。

 

哪些查询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索引细节作为营造脚本。

图片 2

总结

  如上,到那级别简单实用充裕了。们已经介绍了相似的说理,并介绍了搜索数据库中的内容的着力方法。在下一篇中本人将会长远介绍触发器并且找到有效新闻的排序以造福能够透过系统视图从动态在线目录中收载的有用音信。

 

背景

在首先篇中自己介绍了怎样访问元数据,元数据为啥在数据Curry面,以及哪些选择元数据。介绍了什么样获悉各个数据库对象的在数据Curry面包车型地铁名字。第2篇,小编选用了触发器的核心,因为它是一个能提供很好例子的数据库对象,并且在这么些指标中可见建议难点和缓解难题。

本篇作者将会介绍元数据中的索引,不仅仅是因为它们本人很要紧,更要紧的是它们是很好的元数据类型,比如列只怕分布计算,那么些不是元数据中的对象。

目录对于任何关周全据库表都是必备的。可是,仿佛吐司上的黄油一样,过度施用它们大概会在数据库中生出难题。有时,能够对表进行过度索引或缺失索引,只怕营造重复索引。有时难点是选择三个坏的填写因子,错误地设置ignore_dup_key选项,创立3个千古不会被利用(但必须被保证)的目录,丢失外键上的目录,大概将GUID作为主键的一有些。一句话来说,任何频仍利用的数据库系统中的索引都供给定期维护和证明,而目录视图是形成这几个干活儿的最直接的秘籍之1。

相比可行的查询实例

下边小编会展示的例子都早已在二〇〇八和二零一二三个本子中测试。当然只用到了独家版本的末尾三个版本更新后的数据库。

下图中彰显了富有继续sys.objects列的视图。那表示它们除了富有这个列以外,还有列的照应档次。那是视图全数的新闻比如create_date也都出自sys.objects

 图片 3

* *

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

 

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;

 

对此全部其余的,您须要采取一个系统函数来过滤您想要的对象。下边的代码提供了有的实惠的言传身教。因为我们只收获对象的称谓,所以选取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'); 

 

每一个表中有多少个目录,并出示他们的名字

前边的表并不特别有用,因为不能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)

哪些获取上述音讯?

因为我们不能够一贯访问,需求动用视图和函数来看这几个新闻。只赏心悦目看您权力内的数码。有更好的秘籍在用户数据库中选拔数据定义语言(DDL),那一个DDL语句包涵CREATE,
DROP, ALTESportage, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种艺术可以动用DDL来修改视图中的任何新闻,就算并不一连肯定的。

关系型数据库使用动态的体系视图中的数据描述数据库,可是近来还有未有标准化。可是有三个分包在各种数据库内的架构能够读取这几个音讯:就是Information
Schema

不幸运的是,这些架构不足以提供丰富消息,那代表我们必要利用SQL Server
系统数据库的视图和函数来补偿消息。接下来要求说惠氏(WYETH)些术语和技能,笔者会尽或然少的细节足以让我们轻松地知道这几个示例

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

 图片 4

 

* *

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

再有二种比较新鲜的目录,一是空中引得,其音信在sys.spatial_index_tessellations

sys.spatial_indexes表中。另二个是全文索引,其新闻在fulltext_index_fragments,fulltext_index_catalog_usages, fulltext_index_columns
fulltext_indexes表中保留。**

何以是动态在线目录?

  每一个关系型数据库系统,比如SQL Server
一定要提供关于它的结构的新闻,那一个新闻往往需求通过sql语法来查询。平日那些新闻被保存在钦命数据表的结构中。那表示数据库中有三种差别的表:一是用户自定义的表和系统表也许视图(包罗元数据)。从SQL
Server 200五开端,唯有视图能够查询了,不能够直接观察数据表了。

 图片 5

系统视图

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

在数据库内部,有一些系统表一向追踪数据库中生出的每一件事情。系统表存款和储蓄像表、活动、列、索引等作业。这一个完全符合EdgarCodd
的关系型数据库试试的10三条轨道直译。那些规则就是概念动态在线目录,它就是“关于数据的数据”,也叫作元数据。

 埃德加 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中,能够透过系统视图恐怕架构视图直接待上访问动态在线目录,方便用户特别迅猛的付出和管理数据库。

表中都有广大索引吗?

比方您对一些表具有多量索引感到疑虑,那么能够应用上面查询,该查询告诉您具有超过多少个目录和索引计数当先列计数2/4的表。它是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
Central)中使用它们的查询。能够在MSDN上询问到。使用记录工具保存那么些查询。要是它是二个用以保存注释或一些的工具,能够让你在任啥地点方轻松地获得查询,那么它将会有所帮衬。1段时间后,就能够遵照使用须要对那个查询稍作修改。然后,不需求在object
browser窗格中找找表列表,您相当的慢就足以从集合中取得适当的查询,执行它,并快捷获取音讯。

总结

 在数据库中有无数有价值的信息都在目录上。1旦表的多少变大,很不难让表出现有的难题,比如无意中绝非聚集索引或主键,只怕有重新的目录或不须求的总括新闻等。大家通过精晓怎样查询那些索引的动态视图后能够急速查询定位使用表的音信,方便大家预防和缓解那类难点,这一个基础艺术已经在DBA和数据库开发的工作中变得进一步首要了,

目录视图

目录视图提供了关于数据库架构的新闻。它们也被数据库引擎本人笔者使用,特别在询问优化环节。因而那么些视图必要更加快捷的点子来获取元数据。除了复制、备份、数据库维护安排或SQL
Server代理目录数据之外,全数元数据都通过那几个编目视图公开。

那几个视图用一种卓殊新鲜的法子排列,SQL
Server对象的共有音讯都保留在sys.objects里面。有过多派生视图,比如外键、约束、服务队列、表、视图和进度,这个视图用特定于被编目标靶子类型的音信来填补1般的对象音信

无须SQL
Server元数据中的全体内容都以目的。例如,3个列、索引或分布计算消息不是目的。壹些如主键约束或扩大属性有贰个意外的两面性,因为它们被被作为为1个对象,当被挟持键索引的实例化时,它就不是3个对象。有些对象(主如若束缚)与另一种档次的靶子具备父/子关系;父即表。

那些索引占用了多少空间?

设若打算知道索引占了有点空间,有成都百货上千‘胖’索引,就是富含了不少列,有希望索引中部分列不会现出在其余查询中,那正是荒废了上空。

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;

 

图片 6

动态管理视图和功用(DMVs)

DMV一般用来调优,会诊难题和监督检查数据库服务器状态。最要害的功用正是提供了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;

包容性视图

包容性视图是保险元数据的视图,在SQL Server
200五事先是有系统表支持的,并且只向后相当。只在200五现在的版本援助对于某个系统表的询问,例如分区表等,唯有部分元数据可能天性是对用户可知的。对于富含很多用户、群组、剧中人物如故三千版本数据类型的数据库而言,使用包容性视图是有地下风险的,因为视图中有的列存款和储蓄了用户的ID可能项目ID,大概会回到NULL可能触发溢出。

如何查询表使用索引的各个方法?

意识有关索引的少数品质,平日最好利用质量函数作为快速情势。

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

目录存储进程

有不胜枚举存款和储蓄进度的重中之重效能是为SQL
Server的ODBC驱动程序提供元数据音信。当您建立ODBC连接时,该新闻作为数据对象的集结。可是,那几个新闻平常是可用的,并且能够像任何其它存储进度一样从SQL中应用。它们平时被认为不比目录视图有用,因为存款和储蓄进程重返的结果必须接纳INSE卡宴T插入三个表或然表变量中,必要接纳INSE兰德EscortT
… EXECUTE 语法。

干什么元数据视图和效益很要紧?

元数据视图和函数允许你寻找元数据,提供对数据库报告和小结,找出何人有权力查看或变更什么数据,让你减弱重复输入,让大概全数隐藏在SQL
Server Management
Studio的音信可查询,使布置脚本更安全,更可相信,找出以来的更动或创办,神速处理部分函数或进度,明确已注册数据库的本子,审计用于编码实践的数据库代码,发现重复索引并且同意降价扣低效的点击操作。当与任何SQL
Server工具(如默许跟踪和动态管理对象)结合使用时,使用强劲的SQL脚本用于支付和管理数据库是1对①急迅的。

元数据视图和函数允许实施差不多不容许实行的操作,例如查找注重于钦赐的CL奥迪Q5用户定义类型或外号类型的参数。

自笔者批评重复的计算音信

通过相比与各种计算音讯相关联的列号列表,您能够便捷查看同壹列或一组列是或不是有多少个总括消息。

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;

结果如下:

图片 7

来得了含有重复的计算对象,在本例中是sales.customer表在AccountNumber列上有七个8九不离十的总计对象。

元数据function

再有很多元数据函数,如object_name()或col_name(),它们提供有关当前数据库中的情势成效域对象的音讯。通过制止在元数据表明式中开始展览显式连接,它们提供了获取音信的近便的小路,因而,当与编目视图一起利用时,它们能够匡助您越来越快地收获有关元数据的音讯。

怎么样查询每四个表的每2个索引的每三个列?

最简便的查询艺术如下:

SELECT object_schema_name(t.object_ID)+'.'+t.name AS The_Table, --the name of the table
       i.name AS The_Index,  -- its index
       index_column_id,
       col_name(Ic.Object_Id, Ic.Column_Id) AS The_Column --the column
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.object_ID=i.object_ID
INNER JOIN sys.Index_columns  ic
    ON i.Object_ID=ic.Object_ID
    AND i.index_ID=ic.index_ID
ORDER BY t.name,i.index_id, index_column_id;

 

 

图片 8

当然也得以钦点特定表,例如:

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

简介

  在数据库中,大家除了存款和储蓄数据外,还蕴藏了多量的元数据。它们首要的机能正是描述数据库怎么建立、配置、以及种种对象的性质等。本篇简单介绍如何利用和查询元数据,怎样更实用的管制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;

结果如下:

图片 9

何以要去引用sys.tables?那是因为它是确认保障只收获用户表的最简单易行方法。大家接纳index_id
的values大于0,因为要是不为表成立集群索引,在sys中依旧有二个条目。索引,但它指向的是堆,不代表索引。每一种表在sys中都有1行。索引值为0或一的目录。假设该表有一个聚集索引,则有1行数据且index_id值为一;假诺该表是3个堆(那只是象征该表未有聚集索引的另1种艺术),则会有1行的index_id值为0。其余,无论该表是不是有聚集索引,各个非聚集索引都有1行,其index_id值大于一。大家过滤了的目录,这几个索引是由数据库引擎优化顾问(DTA)创制的,指标唯有是测试3个或许的目录是或不是可行。防止它们积累起来,最佳把它们去掉。

设若你过几个七个钦命的表,上面包车型大巴这么些查询是进一步合理的,需求在地点的事例中增添对象的钦点:

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

 

数据层应用程序视图

数据层应用程序视图被用来访问注册服务器消息。特殊版本的服务器和音信用来检查那几个本子是不是漂移。那是壹种作为简单的反省当前登记数据库版本的艺术,直接用T-SQL查询。

每种索引中有个别许行在表里面?

通过连接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;

 

图片 10

下一场,你能够修改那些代码,让其只是展现各样在索引表中的表有多少行。

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;

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

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

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;

效果如下:

图片 11

测算表总的目录空间

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

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;

图片 12

深究索引总计新闻

现行,让大家探究一下分布总括数据或“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;

 

当它们与索引相关联时,总括数据继承索引的称呼,并利用与索引相同的列。

图片 13

留下评论

网站地图xml地图