来自 澳门威尼斯人注册网站 2019-12-04 16:28 的文章
当前位置: 澳门威尼斯人平台 > 澳门威尼斯人注册网站 > 正文

探索SQL Server元数据(一)

背景

  上一篇中,我介绍了SQL Server 允许访问数据库的元数据,为什么有元数据,如何使用元数据。这一篇中我会介绍如何进一步找到各种有价值的信息。以触发器为例,因为它们往往一起很多问题。

 

简介

  在数据库中,我们除了存储数据外,还存储了大量的元数据。它们主要的作用就是描述数据库怎么建立、配置、以及各种对象的属性等。本篇简单介绍如何使用和查询元数据,如何更有效的管理SQLServer 数据库。

  对一些有经验的数据库开发和管理人员而言,元数据是非常有价值的。下面我会介绍一下简单的原理,然后尽量用代码的方式直接说明,毕竟“talk is cheap show me the code ”。

背景

在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,因为它是一个能提供很好例子的数据库对象,并且在这个对象中能够提出问题和解决问题。

本篇我将会介绍元数据中的索引,不仅仅是因为它们本身很重要,更重要的是它们是很好的元数据类型,比如列或者分布统计,这些不是元数据中的对象。

索引对于任何关系数据库表都是必不可少的。然而,就像吐司上的黄油一样,过度使用它们可能会在数据库中产生问题。有时,可以对表进行过度索引或缺失索引,或者构建重复索引。有时问题是选择一个坏的填充因子,错误地设置ignore_dup_key选项,创建一个永远不会被使用(但必须被维护)的索引,丢失外键上的索引,或者将GUID作为主键的一部分。简而言之,任何频繁使用的数据库系统中的索引都需要定期维护和验证,而目录视图是完成这些工作的最直接的方式之一。

from :

那么如何找到触发器的数据?

*  以sys.system_views*is表开始。让我们查询出数据库中使用触发器的信息。可以告知你当前SQL Server版本中有什么触发器。

SELECT schema_name(schema_ID)+'.'+ name

  FROM sys.system_views WHERE name LIKE '%trigger%'

 ----------------------------------------

sys.dm_exec_trigger_stats              

sys.server_trigger_events              

sys.server_triggers                    

sys.trigger_event_types                

sys.trigger_events                     

sys.triggers                           



(6 row(s) affected)

  其中sys.triggers看起来信息很多,它又包含什么列?下面这个查询很容易查到:

 SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id)

  + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

----------------------------------------

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

因此我们多这个信息有了更好的理解,有了一个目录的目录。这个概念有点让人头晕,但是另一方面,它也是相当简单的。我们能够查出元数据,再找个查询中,需要做的就是改变这个单词‘triggers’来查找你想要的视图名称。.

在2012及其以后版本,可以使用一个新的表值函数极大地简化上述查询,并可以避免各种连接。在下面的查询中,我们将查找sys.triggers 视图 中的列。可以使用相同的查询通过更改字符串中的对象名称来获取任何视图的定义。

 SELECT name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

查询结果如下:

 Column_Information

----------------------------------------

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

sys.dm_exec_describe_first_result_set函数的最大优势在于你能看到任何结果的列,不仅仅是表和视图、存储过程或者贬值函数。

为了查出任何列的信息,你可以使用稍微修改的版本,只需要改变代码中的字符串'sys.triggers'即可,如下:

 Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ' + @TheParamater

SELECT

  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

  ORDER BY column_ordinal;

什么是动态在线目录?

  每一个关系型数据库系统,比如SQL Server 一定要提供关于它的结构的信息,这些信息往往需要通过sql语法来查询。通常这些信息被保存在指定数据表的结构中。这意味着数据库中有两种不同的表:一是用户自定义的表和系统表或者视图(包含元数据)。从SQL Server 2005开始,只有视图可以查询了,不能直接看到数据表了。

 澳门威尼斯人注册网站 1

系统视图

这种系统表或者视图的结合通常参考关系型数据库理论的文献叫做作为系统目录或者数据字典。

在数据库内部,有一些系统表一直追踪数据库中发生的每一件事情。系统表存储像表、活动、列、索引等事情。这些完全符合Edgar Codd 的关系型数据库试试的十三条准则直译。这个准则就是定义动态在线目录,它就是“关于数据的数据”,也叫作元数据。

 Edgar Codd  准则4, 描述如下:

‘The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.’

翻译:像普通数据一样,在逻辑层的数据表达了对数据库的描述,以便于授权用户能应用相同的SQL语言来查询元数据,就如同查询常规数据一样。

在SQL Server中,可以通过系统视图或者架构视图直接访问动态在线目录,方便用户更为快捷的开发和管理数据库。

都有哪些索引可以查到?

让我们通过下面的简单语句来看一下都有哪些索引在你的数据库上,代码如下:

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;

结果如下:

澳门威尼斯人注册网站 2

为什么要去引用sys.tables?这是因为它是确保只获得用户表的最简单方法。我们选择index_id 的values大于0,因为如果不为表创建集群索引,在sys中仍然有一个条目。索引,但它指向的是堆,不代表索引。每个表在sys中都有一行。索引值为0或1的索引。如果该表有一个聚集索引,则有一行数据且index_id值为1;如果该表是一个堆(这只是表示该表没有聚集索引的另一种方式),则会有一行的index_id值为0。此外,无论该表是否有聚集索引,每个非聚集索引都有一行,其index_id值大于1。我们过滤了的索引,这些索引是由数据库引擎优化顾问(DTA)创建的,目的仅仅是测试一个可能的索引是否有效。以防它们积累起来,最好把它们去掉。

如果你过一个多个指定的表,下面的这个查询是更为合理的,需要在上面的例子中增加对象的指定:

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

 

元数据简介

但是当然一个触发器是首先是一个对象,因此一定在sys.objects?

  在我们使用sys.triggers的信息之前,需要来重复一遍,所有的数据库对象都存在于sys.objects中,在SQL Server 中的对象包括以下:聚合的CLR函数,check 约束,SQL标量函数,CLR标量函数,CLR表值函数,SQL内联表值函数,内部表,SQL存储过程,CLR存储过程,计划指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,序列对象,服务队列,CLR DML 触发器,SQL表值函数,表类型,用户自定义表,唯一约束,视图和扩展存储过程等。

  触发器是对象所以基础信息一定保存在sys.objects。不走运的是,有时我们需要额外的信息,这些信息可以通过目录视图查询。这些额外数据有是什么呢?

 

  修改我们使用过的查询,来查询sys.triggers的列,这次我们会看到额外信息。这些额外列是来自于sys.objects。

 SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

以上这些让我们知道在sys.triggers的额外信息,但是因为它始终是表的子对象,所以有些不相关信息是不会展示在这些指定的视图或者sys.triggers中的。现在就要带大家去继续找找这些信息。

如何获得以上信息?

因为我们不能直接访问,需要使用视图和函数来看这些信息。只能看到你权限内的数据。有更好的方法在用户数据库中使用数据定义语言(DDL),这些DDL语句包括CREATE, DROP, ALTER, GRANT, DENY, REVOKE 和sp_rename statements 等。总有一种方法可以使用DDL来修改视图中的任何信息,即使并不总是显而易见的。

关系型数据库使用动态的系统视图中的数据描述数据库,但是目前还有没有标准化。但是有一个包含在每个数据库内的架构可以读取这些信息:就是Information Schema

不走运的是,这个架构不足以提供足够信息,这意味着我们需要使用SQL Server 系统数据库的视图和函数来补充信息。接下来需要解释一些术语和技术,我会尽可能少的细节足以让大家轻松地理解这些示例

如图所示,如何访问元数据,及其接口

 澳门威尼斯人注册网站 3

 

* *

每个表中有多少个索引,并展示他们的名字

前面的表并不特别有用,因为无法一眼看到每个表有多少索引,以及它们是什么。下面这个语句可以实现:

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)

元数据 (metadata) 最常见的定义为“有关数据的结构数据”,或者再简单一点就是“关于数据的信息”,日常生活中的图例、图书馆目录卡和名片等都可以看作是元数据。在关系型数据库管理系统 (DBMS) 中,元数据描述了数据的结构和意义。比如在管理、维护 SQL Server 或者是开发数据库应用程序的时候,我们经常要获取一些涉及到数据库架构的信息:

触发器的问题

  触发器是有用的,但是因为它们在SSMS对象资源管理器窗格中不是可见的,所以一般用来提醒错误。触发器有时候会有些微妙的地方让其出问题,比如,当导入过程中禁用了触发器,并且由于某些原因他们没有重启。

下面是一个关于触发器的简要提醒:

  触发器可以在视图,表或者服务器上,任何这些对象上都可以有超过1个触发器。普通的DML触发器能被定义来执行替代一些数据修改(Insert,Update或者Delete)或者在数据修改之后执行。每一个触发器与只与一个对象管理。DDL触发器与数据库关联或者被定义在服务器级别,这类触发器一般在Create,Alter或者Drop这类SQL语句执行后触发。

  像DML触发器一样,可以有多个DDL触发器被创建在同一个T-SQL语句上。一个DDL触发器和语句触发它的语句在同一个事务中运行,所以除了Alter DATABASE之外都可以被回滚。DDL触发器运行在T-SQL语句执行完毕后,也就是不能作为Instead OF触发器使用。

  两种触发器都与事件相关,在DML触发器中,包含INSERT, UPDATE, 和DELETE,然而很多事件都可以与DDL触发器关联,稍后我们将了解。

系统视图

查找没有聚集索引的表

关于索引,您可以找到很多有趣的东西。例如,这里有一种快速查找表的方法,无需使用聚集索引(堆)

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

◆某个数据库中的表和视图的个数以及名称;

在数据库中列出触发器

那么怎么获取触发器列表?下面我在AdventureWorks数据库中进行查询,注意该库的视图中没有触发器。

第一个查询所有信息都在sys.triggers 的目录视图中。

SELECT

  name AS TriggerName,

  coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')') AS TheParent

FROM sys.triggers;



TriggerName                    TheParent

------------------------------ ----------------------------------------

ddlDatabaseTriggerLog          Database (AdventureWorks2012)          

dEmployee                      HumanResources.Employee                

iuPerson                       Person.Person                          

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader         

iduSalesOrderDetail            Sales.SalesOrderDetail                 

uSalesOrderHeader              Sales.SalesOrderHeader                 

dVendor                        Purchasing.Vendor                      

iWorkOrder                     Production.WorkOrder                   

uWorkOrder                     Production.WorkOrder   

  我使用元数据函数db_name()使SQL保持简单。db_name()告诉我数据库的名称。object_schema_name()用来查询object_ID代表的对象的架构,以及object_name**()**查询对象名称。这些对对象的引用指向触发器的所有者,触发器可以是数据库本身,也可以是表:服务器触发器有自己的系统视图,稍后我会展示。

如果想要看到所有触发器,那么我们最好使用sys.objects 视图:

SELECT name as TriggerName, object_schema_name(parent_object_ID)+'.'

    +object_name(parent_object_ID) AS TheParent

            FROM   sys.objects

           WHERE  OBJECTPROPERTYEX(object_id,'IsTrigger') = 1

 

注意,输出不包含数据库级别的触发器,因为所有的DML触发器都在sys.objects视图中,但是你会漏掉在sys.triggers视图中的触发器。

上面查询结果:

name                           TheParent

------------------------------ -------------------------------

dEmployee                      HumanResources.Employee

iuPerson                       Person.Person

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader

iduSalesOrderDetail            Sales.SalesOrderDetail

uSalesOrderHeader              Sales.SalesOrderHeader

dVendor                        Purchasing.Vendor

iWorkOrder                     Production.WorkOrder

uWorkOrder                     Production.WorkOrder

 

Information Schema

这个架构是一套视图,视图中是当前数据库的信息。每一个数据库中都有这个架构,只能看到当前数据库的对象信息。可以直接访问这些架构的数据在主要的关系型数据中。其中架构视图不包含数据库部署信息。

对于不同的关系型数据库之间的处理工作这个架构尤其重要。它们非常适合日常工作,例如在访问钱检查是否存在,但是如果需要详细报告则会受到限制。他们还使用一种稍有不同的标准命名法:例如,数据库被称为目录,用户定义的数据类型被称为“domain”。

之前看到MSDN上有人警告说不要使用INFORMATION_SCHEMA视图来确认对象架构,我理解是因为SQL Server允许在不同的架构中有相同的表名字,因此当只有表名称的时候会有混淆。所以我认为尽管放心使用就好了。

 

每个索引中有多少行在表里面?

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

◆某个表或者视图中列的个数以及每一列的名称、数据类型、长度、精度、描述等;

我的表和视图有多少个触发器?

我想知道每个表有多少个触发器,并且什么情况下触发它们。下面我们列出了具有触发器的表以及每个事件的触发器数量。每个表或者视图对于触发器行为都有一个INSTEAD OF 触发器,可能是UPDATE, DELETE, 或者 INSERT

。但是一个表可以有多个AFTER触发器行为。这些将展示在下面的查询中(排除视图):

SELECT

convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] 

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update'

FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers

      WHERE objectpropertyex(parent_ID, N'IsTable') =1

         GROUP BY parent_ID

          )TablesOnly;

--查询结果如下:

Table                            triggers    Delete Insert Update

-------------------------------- ----------- ------ ------ ------

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

如果超过一个触发器被触发在一个表上,它们不保证顺序,当然也可以使用sp_settriggerorder来控制顺序。通过使用objectpropertyex()元数据函数,需要根据事件输入参数‘ExecIsLastDeleteTrigger’, ‘ExecIsLastInsertTrigger’ 或者 ‘ExecIsLastUpdateTrigger’来确认谁是最后一个执行的触发器 。为了得到第一个触发器,酌情使用ObjectPropertyEx() 元数据函数,需要输入参数 ‘ExecIsFirstDeleteTrigger’, ‘ExecIsFirstInsertTrigger’ 或者 ‘ExecIsFirstUpdateTrigger’。

因此我们现在知道了表有哪些触发器,哪些事件触发这些触发器。可以使用objectpropertyex()元数据函数,这个函数返回很多不同信息,根据指定的参数不同。通过查看MSDN中的文档,查看其中的一个文档是否有助于元数据查询,总是值得检查的。

兼容性视图

兼容性视图是维护元数据的视图,在SQL Server 2005之前是有系统表支持的,并且只向后兼容。只在2005之后的版本支持对于某些系统表的查询,例如分区表等,只有部分元数据或者特性是对用户可见的。对于带有很多用户、群组、角色或者2000版本数据类型的数据库而言,使用兼容性视图是有潜在风险的,因为视图中有的列存储了用户的ID或者类型ID,可能会返回NULL或者触发溢出。

澳门威尼斯人注册网站,表中都有很多索引吗?

如果您对某些表具有大量索引感到怀疑,那么可以使用下面查询,该查询告诉您具有超过4个索引和索引计数超过列计数一半的表。它是一种任意选择具有大量索引的表的方法。

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

 

◆某个表上定义的约束;

触发器何时触发事件?

让我们看一下这些触发器,DML触发器可以在所有其他时间发生后触发,但是可以在约束被处理前并且触发INSTEAD OF触发动作。下面我们就来看看所有的触发的到底是AFTER 还是INSTEAD OF 触发器,有事什么时间触发了触发器。

/* 列出触发器,无论它们是否启用,以及触发器事件。*/

SELECT

  convert(CHAR(25),name) AS triggerName,

  convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS TheParent,

       is_disabled,

       CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END

       +Stuff (--get a list of events for each trigger

        (SELECT ', '+type_desc FROM sys.trigger_events te

           WHERE te.object_ID=sys.triggers.object_ID

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events

 FROM sys.triggers;

结果如下:

triggerName               TheParent                        is_disabled events

------------------------- -------------------------------- ----------- ---------

ddlDatabaseTriggerLog     Database (AdventureWorks2012)    1           AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_XML_INDEX, ALTER_FULLTEXT_INDEX, CREATE_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX, CREATE_SPATIAL_INDEX, CREATE_STATISTICS, UPDATE_STAT

t_AB                      dbo.AB                           0           INSTEAD OF INSERT

dEmployee                 HumanResources.Employee          0           INSTEAD OF DELETE

iuPerson                  Person.Person                    0           AFTER INSERT, UPDATE

iPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER INSERT

uPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER UPDATE

uPurchaseOrderHeader      Purchasing.PurchaseOrderHeader   0           AFTER UPDATE

iduSalesOrderDetail       Sales.SalesOrderDetail           0           AFTER INSERT, UPDATE, DELETE

uSalesOrderHeader         Sales.SalesOrderHeader           0           AFTER UPDATE

dVendor                   Purchasing.Vendor                0           INSTEAD OF DELETE

iWorkOrder                Production.WorkOrder             0           AFTER INSERT

uWorkOrder                Production.WorkOrder             0           AFTER UPDATE

 

As you will notice, we used a FOR XML PATH(‘’) trick here to make a list of the events for each trigger to make it easier to read. These events were pulled from the sys.trigger_events view using a correlated subquery.

注意到我们使用了FOR XML PATH(‘’)来列出事件的每一个触发器,更容易读取理解。sys.trigger_events使用相关子查询来查询这些事件。

目录视图

目录视图提供了关于数据库架构的信息。它们也被数据库引擎自己本身使用,尤其在查询优化环节。因此这些视图需要更高效的方式来获取元数据。除了复制、备份、数据库维护计划或SQL Server代理目录数据之外,所有元数据都通过这些编目视图公开。

这些视图用一种相当特殊的方式排列,SQL Server对象的共有信息都保存在sys.objects里面。有许多派生视图,比如外键、约束、服务队列、表、视图和过程,这些视图用特定于被编目的对象类型的信息来补充一般的对象信息

并非SQL Server元数据中的所有内容都是对象。例如,一个列、索引或分布统计信息不是对象。一些如主键约束或扩展属性有一个奇怪的两面性,因为它们被被当做为一个对象,当被强制键索引的实例化时,它就不是一个对象。有些对象(主要是约束)与另一种类型的对象具有父/子关系;父即表。

查询更新过的索引缺没有使用过有哪些?

总是有必要找出自上次启动服务器以来没有使用的索引,特别是如果服务器一直在做各种各样的工作时。

--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,这里起到了手机使用信息的作用,之后我们会更详尽的使用换这个对象来说明其作用。

◆某个表上定义的索引以及主键/外键的信息。

触发器的多长?

许多数据库人员不赞成冗长触发器的定义,但他们可能会发现,根据定义的长度排序的触发器列表是研究数据库的一种有用方法。

SELECT convert(CHAR(32),coalesce(object_schema_name(t.object_ID)+'.','')

    +name) AS TheTrigger,

       convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS theParent,

       len(definition) AS length --the length of the definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

ORDER BY length DESC;

访问sys.SQL_modules视图可以查看触发器定义的SQL DDL,并按大小顺序列出它们,最上面是最大的。

结果:

TheTrigger                       theParent                        length

-------------------------------- -------------------------------- --------

Sales.iduSalesOrderDetail        Sales.SalesOrderDetail           3666

Sales.uSalesOrderHeader          Sales.SalesOrderHeader           2907

Purchasing.uPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   2657

Purchasing.iPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   1967

Person.iuPerson                  Person.Person                    1498

ddlDatabaseTriggerLog            Database (AdventureWorks2012)    1235

Purchasing.dVendor               Purchasing.Vendor                1103

Production.uWorkOrder            Production.WorkOrder             1103

Purchasing.uPurchaseOrderHeader  Purchasing.PurchaseOrderHeader   1085

Production.iWorkOrder            Production.WorkOrder             1011

HumanResources.dEmployee         HumanResources.Employee          604

 

好吧,我可能太挑剔了,不太喜欢太长的,但是逻辑有时候会很长。事实上,前三名在我看来是不可靠的,尽管我总是倾向于尽可能少地使用触发器。

数据层应用程序视图

数据层应用程序视图被用于访问注册服务器信息。特殊版本的服务器和信息用来检查这些版本是否漂移。这是一种作为容易的检查当前注册数据库版本的方式,直接用T-SQL查询。

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

如果打算知道索引占了多少空间,有许多‘胖’索引,就是包含了很多列,有可能索引中有的列不会出现在任何查询中,这就是浪费了空间。

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

下面我们将介绍几种获取元数据的方法。

这些触发器访问了多少对象

在代码中,每个触发器要访问多少对象(比如表和函数)?

我们只需要检查表达式依赖项。这个查询使用一个视图来列出“软”依赖项(如触发器、视图和函数)。

SELECT coalesce(object_schema_name(parent_id)

          +'.','')+convert(CHAR(32),name) AS TheTrigger,

          count(*) AS Dependencies

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

GROUP BY name, parent_id

ORDER BY count(*) DESC;
--结果:

TheTrigger                               Dependencies

---------------------------------------- ------------

Sales.iduSalesOrderDetail                7

Sales.uSalesOrderHeader                  7

Purchasing.iPurchaseOrderDetail          5

Purchasing.uPurchaseOrderDetail          5

Purchasing.uPurchaseOrderHeader          3

Production.iWorkOrder                    3

Production.uWorkOrder                    3

dbo.t_AB                                 2

Purchasing.dVendor                       2

Person.iuPerson                          2

ddlDatabaseTriggerLog                    1

 

居然有两个触发器有7个依赖!让我们就Sales.iduSalesOrderDetail来实际看一下,有哪些依赖。

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

DMV一般用来调优,诊断问题和监控数据库服务器状态。最重要的作用就是提供了一种方式来查询数据库的使用信息。例如,不仅查询到索引,而且可以查询到使用量的排序和耗时等。

计算表总的索引空间

让我们看看每个表的总索引空间,以及表中的行数。

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

获取元数据

特定触发器访问或者写入哪些对象?

我们可以列出触发器在代码中引用的所有对象

SELECT

  convert(char(32),name) as TheTrigger,

  convert(char(32),coalesce([referenced_server_name]+'.','')

            +coalesce([referenced_database_name]+'.','')

       +coalesce([referenced_schema_name]+'.','')+[referenced_entity_name])
     as referencedObject

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

WHERE name LIKE 'iduSalesOrderDetail';

--查询结果:

TheTrigger                       referencedObject

-------------------------------- --------------------------------

iduSalesOrderDetail              Sales.Customer                 

iduSalesOrderDetail              Person.Person                  

iduSalesOrderDetail              Sales.SalesOrderDetail         

iduSalesOrderDetail              Sales.SalesOrderHeader          

iduSalesOrderDetail              Production.TransactionHistory  

iduSalesOrderDetail              dbo.uspLogError                

iduSalesOrderDetail              dbo.uspPrintError

 

元数据function

还有很多元数据函数,如object_name()或col_name(),它们提供关于当前数据库中的模式作用域对象的信息。通过避免在元数据表达式中进行显式连接,它们提供了获取信息的捷径,因此,当与编目视图一起使用时,它们可以帮助您更快地获取关于元数据的信息。

如何查询表使用索引的各种方式?

发现关于索引的某些属性,通常最好使用属性函数作为快捷方式。

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

使用系统存储过程与系统函数访问元数据

触发器里有什么代码?

现在让我们通过检查触发器的源代码来确认这一点。.

SELECT OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') ); 

我们之前的查询是正确的,扫描源码可知所有的依赖项。大量依赖项表名对于数据库的重构等需要非常小心,例如,修改一个基础表的列。

据需要做什么,您可能希望检查来自元数据视图的定义,而不是使用OBJECT_DEFINITION函数。

 SELECT definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');

目录存储过程

有许多存储过程的主要功能是为SQL Server的ODBC驱动程序提供元数据信息。当您建立ODBC连接时,该信息作为数据对象的集合。但是,这些信息通常是可用的,并且可以像任何其他存储过程一样从SQL中使用。它们通常被认为不如目录视图有用,因为存储过程返回的结果必须使用INSERT插入一个表或者表变量中,需要使用INSERT ... EXECUTE 语法。

为什么元数据视图和功能很重要?

元数据视图和函数允许您搜索元数据,提供对数据库报告和总结,找出谁有权限查看或改变什么数据,让你减少重复输入,让几乎所有隐藏在SQL Server Management Studio的信息可查询,使部署脚本更安全,更可靠,找出最近的改变或创建,快速处理一些函数或过程,确定已注册数据库的版本,审计用于编码实践的数据库代码,发现重复索引并且允许减少低效的点击操作。当与其他SQL Server工具(如默认跟踪和动态管理对象)结合使用时,使用强大的SQL脚本用于开发和管理数据库是相当快速的。

元数据视图和函数允许执行几乎不可能执行的操作,例如查找依赖于指定的CLR用户定义类型或别名类型的参数。

那些是对象,那些不是?

你可能注意到了一些奇怪的事情。尽管表的一些属性(如主键)本身就是对象,但列、统计或索引并非对象。让我们弄清楚这一点,因为它不是完全直观的体现在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 提供的系统存储过程与系统函数。

搜索触发器的代码

There are always plenty of ways of using the metadata views and functions. I wonder if all these triggers are executing that uspPrintError procedure?

有很多使用元数据视图和函数的方法。想知道是否所有这些触发器都执行uspPrintError存储过程?

/* 在所有触发器中搜索字符串 */

 

SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +name) AS TheTrigger, '...'+substring(definition, hit-20,120) +'...'

FROM

  (SELECT name, definition, t.object_ID, charindex('EXECUTE [dbo].[uspPrintError]',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.triggers t

       ON t.object_ID=m.object_ID)f

WHERE hit>0; 

 

结果如图:

澳门威尼斯人注册网站 8

 

8个引用正在执行这个过程。我们在sys.SQL_modules中搜索了所有的定义可以找到一个特定的字符串,这种方式很慢很暴力,但是它是有效的!

我是如何逐渐使用的?

学习使用元数据视图和函数的第一阶段是收集从各种著名的数据源(如SQL Server Central)中使用它们的查询。可以在MSDN上查询到。使用记录工具保存这些查询。如果它是一个用来保存注释或片段的工具,可以让您在任何地方轻松地获取查询,那么它将会有所帮助。一段时间后,就可以根据使用需要对这些查询稍作修改。然后,不需要在object browser窗格中搜索表列表,您很快就可以从集合中获取适当的查询,执行它,并快速获取信息。

如何查询每一个表的每一个索引的每一个列?

最简单的查询方式如下:

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;

 

 

澳门威尼斯人注册网站 9

当然也可以指定特定表,例如:

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

系统存储过程与系统函数在系统表和元数据之间提供了一个抽象层,使得我们不用直接查询系统表就能获得当前数据库对象的元数据。

在所有对象中搜索字符串

我想知道除了触发器之外是否还有其他对象调用这个过程?我们稍微修改查询以搜索sys.objects视图,而不是sys.triggers,以搜索所有具有与之关联的代码的对象。我们还需要显示对象的类型

/* 在所有对象中搜索字符串 */

 SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +object_name(object_ID)) AS TheObject, type_desc, '...'+substring(definition,hit-20,120)+'...' as TheExtract

FROM

  (SELECT  type_desc, definition, o.object_ID, charindex('uspPrintError',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.objects o

       ON o.object_ID=m.object_ID)f

WHERE hit>0; 

查询结果如下图:

澳门威尼斯人注册网站 10

 From this output we can see that, other than the procedure itself where it is defined, and the triggers, only dbo.uspLogError is executing the uspPrintError procedure. (see the first column, second line down)

从这个输出中我们可以看到,除了在定义它的过程本身之外,还有触发器,只有dbo.uspLogError正在执行uspPrintError过程。(见第一列,第二行往下)

比较有用的查询实例

下面我会展示的例子都已经在2008和2012 两个版本中测试。当然只用到了各自版本的最后一个版本更新后的数据库。

下图中展示了所有继承sys.objects列的视图。这意味着它们除了拥有这些列以外,还有列的对应类型。这是视图所有的信息比如create_date也都来自sys.objects

 澳门威尼斯人注册网站 11

* *

要列出数据库中的所有视图(存储过程和外键),只需执行以下操作 …

 

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

 

索引中有哪些列,顺序又是什么 ?

也可以汇总上面语句,每个索引汇总成一行,展示所有索引,具体代码如下:

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;

效果如下:

澳门威尼斯人注册网站 12

常用的与元数据有关的系统存储过程有以下一些:

列出服务器级触发器及其定义

我们可以通过系统视图了解它们吗?嗯,是的。以下是列出服务器触发器及其定义的语句

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

注意,只能看到有权限看的触发器

总结

  如上,到这级别简单实用足够了。们已经介绍了一般的理论,并介绍了查找数据库中的内容的基本方法。在下一篇中我将会深入介绍触发器并且找到有效信息的排序以便于可以通过系统视图从动态在线目录中收集的有用信息。

 

如何查询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索引细节作为构建脚本。

澳门威尼斯人注册网站 13

系统存储过程

总结

  本文讨论过触发器,并且你能查出触发器,以及潜在的问题。这里并没有针对关于触发器的查询提供一个全面的工具箱,因为我只是使用触发器作为示例来展示在查询系统视图时可能使用的一些技术。在我们学习了索引、列和参数之后,我们将回到触发器,并了解了编写访问系统视图和information schema视图的查询的一些日常用途。表是元数据的许多方面的基础。它们是几种类型的对象的父类,其他元数据如索引是表的属性。我们正在慢慢地努力去发现所有关于表的信息。期待下期

元数据中还有其他类型的索引吗?

还有两种比较特殊的索引,一是空间索引,其信息在sys.spatial_index_tessellations 和 sys.spatial_indexes表中。另一个是全文索引,其信息在fulltext_index_fragments,fulltext_index_catalog_usages, fulltext_index_columnsfulltext_indexes表中保存。**

◆sp_columns 返回指定表或视图的列的详细信息。

探索索引统计信息

现在,让我们讨论一下分布统计数据或“stats”。每个索引都有一个附加的统计对象,以便查询优化器能够提供一个合适的查询计划。为此,它需要估计数据的“基数”,以确定为任何索引值返回多少行,并使用这些“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;

 

当它们与索引相关联时,统计数据继承索引的名称,并使用与索引相同的列。

澳门威尼斯人注册网站 14

◆sp_databases 返回当前服务器上的所有数据库的基本信息。

检查重复的统计信息

通过比较与每个统计信息相关联的列号列表,您可以快速查看同一列或一组列是否有多个统计信息。

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;

结果如下:

澳门威尼斯人注册网站 15

展示了包含重复的统计对象,在本例中是sales.customer表在AccountNumber列上有两个类似的统计对象。

◆sp_fkeys 若参数为带有主键的表,则返回包含指向该表的外键的所有表;若参数为带有外键的表名,则返回所有同过主键/外键关系与该外键相关联的所有表。

总结

 在数据库中有很多有价值的信息都在索引上。一旦表的数量变大,很容易让表出现一些问题,比如无意中没有聚集索引或主键,或者有重复的索引或不必要的统计信息等。我们通过掌握如何查询这些索引的动态视图后能够快速查询定位使用表的信息,方便我们预防和解决这类问题,这些基础方法已经在DBA和数据库开发的工作中变得越来越重要了,

◆sp_pkeys 返回指定表的主键信息。

◆sp_server_info 返回当前服务器的各种特性及其对应取值。

本文由澳门威尼斯人平台发布于澳门威尼斯人注册网站,转载请注明出处:探索SQL Server元数据(一)

关键词: