訪問 Microsoft SQL Server 中繼資料的三種方法

 

上海微創軟體有限公司 肖桂東

適用讀者：Microsoft SQL Server 中、進階使用者

中繼資料簡介

中繼資料 (metadata) 最常見的定義為有關資料的結構資料，或者再簡單一點就是關於資料的資訊，日常生活中的圖例、圖書館目錄卡和名片等都可以看作是中繼資料。在關係型資料庫管理系統 (DBMS) 中，中繼資料描述了資料的結構和意義。比如在管理、維護 SQL Server 或者是開發資料庫應用程式的時候，我們經常要擷取一些涉及到資料庫結構描述的資訊：

1. 某個資料庫中的表和視圖的個數以及名稱 ；
2. 某個表或者視圖中列的個數以及每一列的名稱、資料類型、長度、精度、描述等；
3. 某個表上定義的約束；
4. 某個表上定義的索引以及主鍵/外鍵的資訊。

下面我們將介紹幾種擷取中繼資料的方法。

擷取中繼資料

使用系統預存程序與系統函數訪問中繼資料

擷取中繼資料最常用的方法是使用 SQL Server 提供的系統預存程序與系統函數。

系統預存程序與系統函數在系統資料表和中繼資料之間提供了一個抽象層，使得我們不用直接查詢系統資料表就能獲得當前資料庫物件的中繼資料。

常用的與中繼資料有關的系統預存程序有以下一些：

常用的與中繼資料有關的系統函數有以下一些：

由於我們無法直接利用到預存程序與函數的返回結果，因此只有在我們關心的只是查詢的結果，而不需要進一步利用這些結果的時候，我們會使用系統預存程序與系統函數來查詢中繼資料。

例如，如果要獲得當前伺服器上所有資料庫的基本資料，我們可以在查詢分析器裡面運行：

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-92 標準中針對架構視圖的定義，這些視圖獨立於系統資料表，提供了關於 SQL Server 中繼資料的內部視圖。資訊架構視圖的最大優點是，即使我們對系統資料表進行了重要的修改，應用程式也可以正常地使用這些視圖進行訪問。因此對於應用程式來說，只要是符合 SQL-92 標準的資料庫系統，使用資訊架構視圖總是可以正常工作的。

常用的資訊架構視圖有以下一些：

由於這些資訊架構都是以視圖的方式存在的，因此我們可以很方便地獲得並利用需要的資訊。

例如，我們要得到某個表有多少列，可以使用以下語句：

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