軟件測試中的訪問 Microsoft SQL Server 元數據的三種方法

軟件測試中的訪問 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 上通過。

參考資料

1. Microsoft SQL Server 2000 聯機叢書
   
2. http://www.microsoft.com/china/sql
   
3. http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409

文章來源于領測軟件測試網 http://www.kjueaiud.com/

TAG: microsoft Microsoft MicrOSoft server Server SERVER sql SQL Sql 軟件測試 數據