SQL Server的系统表及其应用

http://www.ithome-cn.net/technology/data/data027.htm

　　摘要：本文介绍了Microsoft SQL Server数据库的系统表，并对系统中经常遇到的一个问题给出了通过系统表建立存储过程的解决办法。

　　关键词：SQL Server 系统表 存储过程


　　一、SQL Server的系统表


　　Microsoft 的SQL Server是一个可伸缩的高性能数据库管理系统，专为分布式客户机/服务器环境而设计，SQL Server几乎将所有的配置信息、安全性信息和对象信息都存储在了它自身的系统表中，而系统表存在于每个独立的数据库中，存储一个特定数据库对象信息的系统表通常称为数据库目录，MASTER数据库有其特有的系统表用于保存整个系统和所有数据库的信息，通常称为服务器目录或系统目录。

　　服务器上所有的数据库包括MODULE，MASTER等都含有18个具有相同名称、结构的系统表，如表SYSOBJECTS用于描述数据库中的对象--表、视图、存储过程等，表SYSUSER用于描述数据库的用户，而MASTER数据库另外还有13个单独的全局系统表，如表SYSLOGINS用于保存每个服务器的登录名、口令和配置信息、表SYSDATABASE保存服务器上所有数据库名、所有者、状态及其他信息。

　　存储过程是内嵌于数据库中的程序代码，它与表、视图等一样是数据库的一个组成部分，不同于一般的外部程序代码，它是经过预编译处理的代码，因此具有运行速度快、效率高的特点，存储过程也是SQL Server的一个重要功能，许多单纯针对后台数据库的操作一般都交由存储过程来完成以提高系统效率。本文将给出一个存储过程来说明SQL Server系统表的应用。


　　二、SQL Server系统表的应用


　　在应用SQL Server的基于客户机/服务器体系结构的信息系统开发中，有时需要将后台SQL Server上的某一数据库的表结构都打印出来，以便于开发人员查阅及最终文档的形成。SQL Server本身提供了一个系统存储过程（SP_COLUMNS）,可以完成对单个表结构的查询，只要在SQL Server的ISQL-W工具中键入SP_COLUMNS “表名”，并执行即可得到结果集。但该方法有许多不足之处，其主要缺点是：

　　1) 只能对单表进行操作，当需要查询一个数据库中所有的表时，需要多次执行SP_COLUMNS ，因此显得非常繁琐。

　　2) 查询结果集中包含了许多不必要的信息。

　　下面我们创建一个存储过程来完成对某一个数据库中所有表结构的查询。

　　在创建一个数据库的同时，系统会自动建立一些系统表，限于篇幅的缘故我们在这里只介绍与应用实例有关的三个系统表（SYSOBJECTS，SYSCOLUMNS，SYSTYPES）及其相关的字段。

　　表SYSOBJECTS为数据库内创建的每个对象（约束，规则，表，视图，触发器等）创建一条记录。

　　该表相关字段的含义如下：

　　SYSOBJECTS.name 对象名，如：表名，视图名。

　　SYSOBJECTS.id 对象id。

　　SYSOBJECTS.type 对象类型（p存储过程，v视图，s系统表，u用户表）。

　　表SYSCOLUMNS 为每个表、视图中的每个列和每个存储过程的每个参数创建一条记录。

　　该表相关字段的含义如下：（此处的列系指数据库中每个表、视图中的列）

　　SYSCOLUMNS. id 该列所属的表的id，可与SYSOBJECTS.id相关联

　　SYSCOLUMNS.colid 列id，表示该列是表或视图的第几列。

　　SYSCOLUMNS.type 物理存储类型，可与SYSTYPES.type相关联。

　　SYSCOLUMNS.length 数据的物理长度。

　　SYSCOLUMNS.name 列名字，即字段名。

　　SYSCOLUMNS .Pre 列的精度级。

　　SYSCOLUMNS .Scale 列的标度级。

　　表SYSTYPES 为每个系统和每个用户提供的数据类型创建一条记录，如果它们存在，给定域和默认值，描述系统提供的数据类型的行不可更改。

　　该表相关字段的含义如下：

　　SYSTYPES.name 数据类型的名字。

　　SYSTYPES.type 物理存储数据类型。

　　在SQL SERVER的企业管理器(SQL ENTERPRISE MANAGER)中,选定某一数据库，创建存储过程print_dbstructure。

　　源代码如下:

　　if exists (select * from sysobjects where id = object_id('dbo. print_dbstructure

　　') and sysstat & 0xf = 4) 存储过程

　　drop procedure dbo. print_dbstructure

　　

　　GO

　　CREATE PROCEDURE print_dbstructure

　　

　　AS

　　SELECT DISTINCT sysobjects.name, syscolumns.colid,

　　syscolumns.name, systypes.name, syscolumns.prec, syscolumns.scale

　　FROM syscolumns, sysobjects, systypes

　　WHERE sysobjects.id = syscolumns.id AND systypes.type = syscolumns.type AND ((sysobjects.type='u'))

　　

　　GO

　　首先判断是否存在一个名为print_dbstructure的存储过程，如果存在，就摘除它,否则，定义SQL语句建立新的存储过程。从三个系统表中选出满足条件的记录（即该数据库中保存在系统表中的用户表信息）。

　　执行时，在ISQL_W工具中，选定print_dbstructure所在的数据库，执行该存储过程，即可得到结果集（即该数据库中用户表的结构信息）。


　　三、SQL Server系统表的推广应用


　　以上所介绍存储过程的主要缺陷在于只能对服务器上某一特定的数据库（print_dbstructure所在的数据库）进行查询操作，我们可以通过在SQL Server系统数据库MASTER中建立带有数据库名参数的系统存储过程来解决这一问题，请读者自行完成。当然，SQL Server 系统表的应用远不止于此，读者也可依本文所介绍的方法去挖掘其他几个系统表的应用。