SQL2000系统表、存储过程、函数的功能介绍及应用

<?XML:NAMESPACE PREFIX = O />

----系统表--------------------------------------------------------------------------------------

虽然使用系统存储过程、系统函数与信息架构视图已经可以为我们提供了相当丰富的元数据信息，但是对于某些特殊的元数据信息，我们仍然需要直接对系统表进行查询。因为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

----系统存储过程---------------------------------------------------------------------------

----系统函数----------------------------------------------------------------------------------------------------

----使用信息架构视图访问元数据---------------------------------------------------------------------------------------------------

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

常用的信息架构视图有以下一些：

由于这些信息架构都是以视图的方式存在的，因此我们可以很方便地获得并利用需要的信息。

例如，我们要得到某个表有多少列，可以使用以下语句：

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME='mytable'

----应用----------------------------------------------------------------------

--1：获取当前数据库中的所有用户表

select Name from sysobjects where xtype='u' and status>=0

--2：获取某一个表的所有字段

select name from syscolumns where id=object_id(N'表名')

--3：查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like N'%表名%'

--4：查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

--5：查询用户创建的所有数据库

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

或者

select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

--6：查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns

where table_name = N'表名'

--7:获取数据库文件路径

select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('MDF',filename)>0

or

select ltrim(rtrim(filename)) from 数据库名..sysfiles where charindex('LDF',filename)>0

--8:获取某一个表的基本信息

sp_MShelpcolumns N'表名'

--9:获取某一个表的主键、外键信息

exec sp_pkeys N'表名'

exec sp_fkeys  N'表名'

--10:判断某一个表是否存在某一列(字段)

if exists(select 1 from syscolumns where id=object_id(N'表名) and name=N'字段')

    print  N'存在'

else

    print  N'不存在'

下面给出了一个存储过程，它的作用是自动将当前数据库的用户存储过程加密。

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 上通过。