SQLServer系統資料表及其應用

 

　　　關鍵詞：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 系統資料表的應用遠不止於此，讀者也可依本文所介紹的方法去挖掘其他幾個系統資料表的應用。　轉自： http://www.ithome-cn.net/technology/data/data027.htm