探索SQL Server元数据（一）

大家好，又见面了，我是全栈君，祝每个程序员都可以多学几门语言。

简介

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

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

什么是动态在线目录?

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

系统视图

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

在数据库内部，有一些系统表一直追踪数据库中发生的每一件事情。系统表存储像表、活动、列、索引等事情。这些完全符合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中，可以通过系统视图或者架构视图直接访问动态在线目录，方便用户更为快捷的开发和管理数据库。

如何获得以上信息?

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

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

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

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

系统视图

Information Schema

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

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

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

兼容性视图

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

目录视图

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

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

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

数据层应用程序视图

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

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

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

元数据function

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

目录存储过程

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

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

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

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

我是如何逐渐使用的?

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

比较有用的查询实例

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

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

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

代码语言：javascript复制

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,

代码语言：javascript复制

object_schema_name(referenced_object_ID) ’.’ object_name(referenced_object_ID) AS referenced

代码语言：javascript复制

FROM sys.foreign_keys;

对于所有其他的，您需要使用一个系统函数来过滤您想要的对象。下面的代码提供了一些有用的示例。因为我们只获取对象的名称，所以使用sys.objects，它具有所有数据库对象共有的基本信息的视图。如果我们需要特定于特定类型对象的信息，比如主键是否具有系统生成的名称，那么您就必须为该特定类型的对象使用视图。

代码语言：javascript复制

/* 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 

代码语言：javascript复制

/* 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;

当然我们也可以调整这些语句来方便我们的精确查找，比如:

–数据库中的所有视图在过去两周内被修改的有：

代码语言：javascript复制

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

–上个月创建的所有对象的名称和类型

代码语言：javascript复制

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架构中所有基本对象的名称和类型

代码语言：javascript复制

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

总结

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

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/119612.html原文链接：https://javaforall.cn

sqlserver 数据库 sql

0 人点赞