SQL Server获取元数据所有方法和示例

落日映苍穹つ 2023-02-15 13:53 1阅读 0赞

### SQL Server元数据 ###

*  什么是元数据？
 *  怎么获取元数据？
 *   *  使用系统存储过程
     *  使用系统函数
     *  使用系统表
     *  使用信息架构视图

# 什么是元数据？ #

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

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

# 怎么获取元数据？ #

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

示例数据库AdventureWorks2008：[github下载地址][github]

## 使用系统存储过程 ##

元数据常用存储过程

<table> 
 <thead> 
  <tr> 
   <th align="center">系统存储过程</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms176077%28v%3dsql.110%29" rel="nofollow">sp_columns</a></td> 
   <td align="left">返回指定表或视图的列的详细信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms176070%28v=sql.110%29" rel="nofollow">sp_databases</a></td> 
   <td align="left">返回当前服务器上的所有数据库的基本信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms175090%28v=sql.110%29" rel="nofollow">sp_fkeys</a></td> 
   <td align="left">若参数为带有主键的表，则返回包含指向该表的外键的所有表；若参数为带有外键的表名，则返回所有同过主键/外键关系与该外键相关联的所有表。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms189813%28v=sql.110%29" rel="nofollow">sp_pkeys</a></td> 
   <td align="left">返回指定表的主键信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms176094%28v=sql.110%29" rel="nofollow">sp_server_info</a></td> 
   <td align="left">返回当前服务器的各种特性及其对应取值。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms182705%28v=sql.110%29" rel="nofollow">sp_sproc_columns</a></td> 
   <td align="left">返回指定存储过程的的输入、输出参数的信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms173842%28v=sql.110%29" rel="nofollow">sp_statistics</a></td> 
   <td align="left">返回指定的表或索引视图上的所有索引以及统计的信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms190504%28v=sql.110%29" rel="nofollow">sp_stored_procedures</a></td> 
   <td align="left">返回当前数据库的存储过程列表，包含系统存储过程。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms186250%28v=sql.110%29" rel="nofollow">sp_tables</a></td> 
   <td align="left">返回当前数据库的所有表和视图，包含系统表。</td> 
  </tr> 
 </tbody> 
</table>

**示例**

USE AdventureWorks2008
    GO
    execute sp_columns 'Person'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70]

execute sp_tables

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 1]

## 使用系统函数 ##

[元数据函数详细文档][Link 1]

<table> 
 <thead> 
  <tr> 
   <th align="center">系统函数</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">COLUMNPROPERTY</td> 
   <td align="left">返回有关列或过程参数的信息，如是否允许空值，是否为计算列等。</td> 
  </tr> 
  <tr> 
   <td align="center">COL_LENGTH</td> 
   <td align="left">返回指定数据库的指定属性值，如是否处于只读模式等。</td> 
  </tr> 
  <tr> 
   <td align="center">DATABASEPROPERTYEX</td> 
   <td align="left">返回指定数据库的指定选项或属性的当前设置，如数据库的状态、恢复模型等。</td> 
  </tr> 
  <tr> 
   <td align="center">OBJECT_ID</td> 
   <td align="left">返回指定数据库对象名的标识号</td> 
  </tr> 
  <tr> 
   <td align="center">OBJECT_NAME</td> 
   <td align="left">返回指定数据库对象标识号的对象名。</td> 
  </tr> 
  <tr> 
   <td align="center">OBJECTPROPERTY</td> 
   <td align="left">返回指定数据库对象标识号的有关信息，如是否为表，是否为约束等。</td> 
  </tr> 
  <tr> 
   <td align="center">fn_listextendedproperty</td> 
   <td align="left">返回数据库对象的扩展属性值，如对象描述、格式规则、输入掩码等。</td> 
  </tr> 
 </tbody> 
</table>

**示例**

SELECT COLUMNPROPERTY( OBJECT_ID('Person.Contact'),'LastName','PRECISION')AS 'Column Length';

![在这里插入图片描述][20200606003531909.png]

USE AdventureWorks2008
    GO
    IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') = 1
       PRINT 'UnitMeasure is a table.'
    ELSE IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') = 0
       PRINT 'UnitMeasure is not a table.'
    ELSE IF OBJECTPROPERTY (OBJECT_ID(N'Production.UnitMeasure'),'ISTABLE') IS NULL
       PRINT 'ERROR: UnitMeasure is not a valid object.';
    GO

![在这里插入图片描述][20200606002511925.png]

## 使用系统表 ##

元数据常用系统表

<table> 
 <thead> 
  <tr> 
   <th align="center">系统表</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms186293%28v=sql.100%29" rel="nofollow">syscolumns</a></td> 
   <td align="left">存储每个表和视图中的每一列的信息以及存储过程中的每个参数的信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms186293%28v=sql.100%29" rel="nofollow">syscomments</a></td> 
   <td align="left">存储包含每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的原始 SQL 文本语句。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms190365%28v=sql.100%29" rel="nofollow">sysconstraints</a></td> 
   <td align="left">存储当前数据库中每一个约束的基本信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms179900%28v=sql.100%29" rel="nofollow">sysdatabases</a></td> 
   <td align="left">存储当前服务器上每一个数据库的基本信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms190283%28v=sql.100%29" rel="nofollow">sysindexes</a></td> 
   <td align="left">存储当前数据库中的每个索引的信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms177596%28v%3dsql.100%29" rel="nofollow">sysobjects</a></td> 
   <td align="left">存储数据库内的每个对象（约束、默认值、日志、规则、存储过程、用户表等）的基本信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms186900%28v=sql.100%29" rel="nofollow">sysreferences</a></td> 
   <td align="left">存储所有包括 FOREIGN KEY 约束的列。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms175109%28v=sql.100%29" rel="nofollow">systypes</a></td> 
   <td align="left">存储系统提供的每种数据类型和用户定义数据类型的详细信息。</td> 
  </tr> 
 </tbody> 
</table>

**示例**

select * from sysobjects where OBJECT_NAME(ID)='Department';

![在这里插入图片描述][20200606003635848.png]

select * from  sysdatabases;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 2]

## 使用信息架构视图 ##

> [**信息架构视图**][Link 2]基于 SQL-92 标准中针对架构视图的定义，这些视图独立于系统表，提供了关于 SQL Server 元数据的内部视图。信息架构视图的最大优点是，即使我们对系统表进行了重要的修改，应用程序也可以正常地使用这些视图进行访问。因此对于应用程序来说，只要是符合 SQL-92 标准的数据库系统，使用信息架构视图总是可以正常工作的。

常用视图

<table> 
 <thead> 
  <tr> 
   <th align="center">视图</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms189772%28v%3dsql.90%29" rel="nofollow">INFORMATION_SCHEMA .CHECK_CONSTRAINTS</a></td> 
   <td align="left">返回有关列或过程参数的信息，如是否允许空值，是否为计算列等。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms188348%28v%3dsql.105%29" rel="nofollow">INFORMATION_SCHEMA .COLUMNS</a></td> 
   <td align="left">返回当前数据库中当前用户可以访问的所有列及其基本信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms174431%28v%3dsql.105%29" rel="nofollow">INFORMATION_SCHEMA .CONSTRAINT_COLUMN_USAGE</a></td> 
   <td align="left">返回当前数据库中定义了约束的所有列及其约束名。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms179883%28v%3dsql.105%29" rel="nofollow">INFORMATION_SCHEMA .CONSTRAINT_TABLE_USAGE</a></td> 
   <td align="left">返回当前数据库中定义了约束的所有表及其约束名。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms189789%28v=sql.105%29" rel="nofollow">INFORMATION_SCHEMA .KEY_COLUMN_USAGE</a></td> 
   <td align="left">返回当前数据库中作为主键/外键约束的所有列。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms182642%28v=sql.105%29" rel="nofollow">INFORMATION_SCHEMA .SCHEMATA</a></td> 
   <td align="left">返回当前用户具有权限的所有数据库及其基本信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms186224%28v=sql.105%29" rel="nofollow">INFORMATION_SCHEMA .TABLES</a></td> 
   <td align="left">返回当前用户具有权限的当前数据库中的所有表或者视图及其基本信息。</td> 
  </tr> 
  <tr> 
   <td align="center"><a href="https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms181381%28v=sql.105%29" rel="nofollow">INFORMATION_SCHEMA .VIEWS</a></td> 
   <td align="left">返回当前数据库中的当前用户可</td> 
  </tr> 
 </tbody> 
</table>

**示例**

select * from INFORMATION_SCHEMA .CHECK_CONSTRAINTS;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 3]

select * from INFORMATION_SCHEMA .TABLES

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 4]

> **原创不易，请勿转载**（本不富裕的访问量雪上加霜 ）  
> 博主首页：[https://blog.csdn.net/qq\_45034708][https_blog.csdn.net_qq_45034708]  
> *如果文章对你有帮助，记得关注点赞收藏❤*

[github]: https://github.com/wuzelong/CSDNBLOG
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020060600291639.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200606002945928.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4,size_16,color_FFFFFF,t_70
[Link 1]: https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms187812%28v%3dsql.90%29
[20200606003531909.png]: https://img-blog.csdnimg.cn/20200606003531909.png
[20200606002511925.png]: https://img-blog.csdnimg.cn/20200606002511925.png
[20200606003635848.png]: https://img-blog.csdnimg.cn/20200606003635848.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200606003829225.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4,size_16,color_FFFFFF,t_70
[Link 2]: https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms186778%28v=sql.100%29
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200606003900961.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200606003921109.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MDM0NzA4,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_qq_45034708]: https://blog.csdn.net/qq_45034708