获得SQL Server数据库元数据的几种方法
元数据简介
元数据 (metadata) 最多见的定义为“有关数据的结构数据”，或再简单一点就是“关于数据的信息”，平常生活中的图例、图书馆目录卡和名片等都可以看做是元数据。在关系型数据库管理系统 (DBMS) 中，元数据描写了数据的结构和意义。比如在管理、保护 SQL Server 或是开发数据库利用程序的时候，我们常常要获得一些触及到数据库架构的信息：
◆某个数据库中的表和视图的个数和名称；
◆某个表或视图中列的个数和每列的名称、数据类型、长度、精度、描写等；
◆某个表上定义的束缚；
◆某个表上定义的索引和主键/外键的信息。
下面我们将介绍几种获得元数据的方法。
获得元数据
使用系统存储进程与系统函数访问元数据
获得元数据最经常使用的方法是使用 SQL Server 提供的系统存储进程与系统函数。
系统存储进程与系统函数在系统表和元数据之间提供了一个抽象层，使得我们不用直接查询系统表就可以取得当前数据库对象的元数据。
经常使用的与元数据相关的系统存储进程有以下一些：
系统存储进程
◆sp_columns 返回指定表或视图的列的详细信息。
◆sp_databases 返回当前服务器上的所有数据库的基本信息。
◆sp_fkeys 若参数为带有主键的表，则返回包括指向该表的外键的所有表；若参数为带有外键的表名，则返回所有同过主键/外键关系与该外键相关联的所有表。
◆sp_pkeys 返回指定表的主键信息。
◆sp_server_info 返回当前服务器的各种特性及其对应取值。
◆sp_sproc_columns 返回指定存储进程的的输入、输出参数的信息。
◆sp_statistics 返回指定的表或索引视图上的所有索引和统计的信息。
◆sp_stored_procedures 返回当前数据库的存储进程列表，包括系统存储进程。
◆sp_tables 返回当前数据库的所有表和视图，包括系统表。
经常使用的与元数据相关的系统函数有以下一些：
系统函数
◆COLUMNPROPERTY 返回有关列或进程参数的信息，如会不会允许空值，会不会为计算列等。
◆COL_LENGTH 返回指定数据库的指定属性值，如会不会处于只读模式等。
◆DATABASEPROPERTYEX 返回指定数据库的指定选项或属性确当前设置，如数据库的状态、恢复模型等。
◆OBJECT_ID 返回指定数据库对象名的标识号
◆OBJECT_NAME 返回指定数据库对象标识号的对象名。
◆OBJECTPROPERTY 返回指定数据库对象标识号的有关信息，如会不会为表，会不会为束缚等。
◆fn_listextendedproperty 返回数据库对象的扩大属性值，如对象描写、格式规则、输入掩码等。
由于我们没法直接利用到存储进程与函数的返回结果，因此只有在我们关心的只是查询的结果，而不需要进一步利用这些结果的时候，我们会使用系统存储进程与系统函数来查询元数据。
例如，如果要取得当前服务器上所有数据库的基本信息，我们可以在查询分析器里面运行：
EXEC sp_databases
GO
在返回结果中我们可以看到数据库的名称、大小及备注等信息。
但是如果要援用这部份信息，或存储这部份信息以供后面使用，那末我们一定要借助中间表来完成这个操作：
CREATE TABLE #sp_result
(
DATABASE_NAME sysname,
DATABASE_SIZE int,
REMARKS varchar(254) NULL
)
GO
INSERT INTO #sp_result
EXEC (‘sp_databases’)
GO
使用信息架构视图访问元数据
信息架构视图基于 SQL⑼2 标准中针对架构视图的定义，这些视图独立于系统表，提供了关于 SQL Server 元数据的内部视图。信息架构视图的最大优点是，即便我们对系统表进行了重要的修改，利用程序也能够正常地使用这些视图进行访问。因此对利用程序来讲，只要是符合 SQL⑼2 标准的数据库系统，使用信息架构视图总是可以正常工作的。
信息架构视图
◆INFORMATION_SCHEMA.CHECK_CONSTRAINTS：返回有关列或进程参数的信息，如会不会允许空值，会不会为计算列等。
◆INFORMATION_SCHEMA.COLUMNS：返回当前数据库中当前用户可以访问的所有列及其基本信息。
◆INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE：返回当前数据库中定义了束缚的所有列及其束缚名。
◆INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE：返回当前数据库中定义了束缚的所有表及其束缚名。
◆INFORMATION_SCHEMA.KEY_COLUMN_USAGE：返回当前数据库中作为主键/外键束缚的所有列。
◆INFORMATION_SCHEMA.SCHEMATA：返回当前用户具有权限的所有数据库及其基本信息。
◆INFORMATION_SCHEMA.TABLES：返回当前用户具有权限确当前数据库中的所有表或视图及其基本信息。
◆INFORMATION_SCHEMA.VIEWS：返回当前数据库中确当前用户可以访问的视图及其所有者、定义等信息。
由于这些信息架构都是以视图的方式存在的，因此我们可以很方便地取得并利用需要的信息。
例如，我们要得到某个表有多少列，可使用以下语句：
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=’mytable’
使用系统表访问元数据
虽然使用系统存储进程、系统函数与信息架构视图已可以为我们提供了相当丰富的元数据信息，但是对某些特殊的元数据信息，我们依然需要直接对系统表进行查询。由于SQL Server 将所有数据库对象的信息均寄存在系统表中，作为 SQL Server 的管理、开发人员，了解各个系统表的作用将有助于我们了解 SQL Server 的内在工作原理。
SQL Server 的系统表非常多，其中最经常使用的与元数据查询有关的表有以下一些：
syscolumns 存储每一个表和视图中的每列的信息和存储进程中的每一个参数的信息。
syscomments 存储包括每一个视图、规则、默许值、触发器、CHECK 束缚、DEFAULT 束缚和存储进程的原始 SQL 文本语句。
sysconstraints 存储当前数据库中每个束缚的基本信息。
sysdatabases 存储当前服务器上每个数据库的基本信息。
sysindexes 存储当前数据库中的每一个索引的信息。
sysobjects 存储数据库内的每一个对象（束缚、默许值、日志、规则、存储进程等）的基本信息。
sysreferences 存储所有包括 FOREIGN KEY 束缚的列。
systypes 存储系统提供的每种数据类型和用户定义数据类型的详细信息。
将系统存储进程、系统函数、信息架构视图与系统表结合使用，可以方便地让我们取得所有需要的元数据信息。
示例：
1、 取得当前数据库所有用户表的名称。
SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = ‘U’ AND OBJECTPROPERTY (id, ‘IsMSShipped’) = 0
其中主要用到了系统表 sysobjects和其属性 xtype，还有就是用到了 OBJECTPROPERTY 系统函数来判断是不是是安装 SQL Server 的进程中创建的对象。
2、取得指定表上所有的索引名称。
SELECT name FROM sysindexes
WHERE id = OBJECT_ID (‘mytable’) AND indid > 0
综合实例
下面给出了一个存储进程，它的作用是自动将当前数据库的用户存储进程加密。
DECLARE @sp_name nvarchar(400)
DECLARE @sp_content nvarchar(2000)
DECLARE @asbegin int
declare @now datetime
select @now = getdate()
DECLARE sp_cursor CURSOR FOR
SELECT object_name(id)
FROM sysobjects
WHERE xtype = ‘P’
AND type = ‘P’
AND crdate < @now
AND OBJECTPROPERTY(id, ‘IsMSShipped’)=0
OPEN sp_cursor
FETCH NEXT FROM sp_cursor
INTO @sp_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp_content = text FROM
syscomments WHERE id = OBJECT_ID(@sp_name)
SELECT @asbegin =
PATINDEX ( ‘%AS’ + char(13) + ‘%’, @sp_content)
SELECT @sp_content =
SUBSTRING(@sp_content, 1, @asbegin – 1)
+ ‘ WITH ENCRYPTION AS’
+ SUBSTRING (@sp_content, @asbegin+2, LEN(@sp_content))
SELECT @sp_name = ‘DROP PROCEDURE [‘ + @sp_name + ‘]’
EXEC sp_executesql @sp_name
EXEC sp_executesql @sp_content
FETCH NEXT FROM sp_cursor
INTO @sp_name
END
CLOSE sp_cursor
DEALLOCATE sp_cursor
该存储进程利用了 sysobjects 和 syscomments 表，并奇妙地修改了原存储进程的 SQL 定义语句，将 AS 修改成了 WITH ENCRYPTION AS，从而到达了加密存储进程的目的。本存储进程在 SQL Server 2000 上通过。
文章来源：丸子建站
文章标题：获得SQL Server数据库元数据的几种方法
https://www.wanzijz.com/view/69201.html
