由于这些信息架构都是以视图的方式存在的,因此我们可以很方便地获得并利用需要的信息。
例如,我们要得到某个表有多少列,可以使用以下语句:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='mytable'
使用系统表访问元数据
虽然使用系统存储过程、系统函数与信息架构视图已经可以为我们提供了相当丰富的元数据信息,但是对于某些特殊的元数据信息,我们仍然需要直接对系统表进行查询。因为SQL Server 将所有数据库对象的信息均存放在系统表中,作为 SQL Server 的管理、开发人员,了解各个系统表的作用将有助于我们了解 SQL Server 的内在工作原理。
SQL Server 的系统表非常多,其中最常用的与元数据查询有关的表有如下一些:
将系统存储过程、系统函数、信息架构视图与系统表结合使用,可以方便地让我们获得所有需要的元数据信息。
示例:
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 上通过。
<