獲取SQL Server數據庫元數據的方法

元數據簡介

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

◆某個數據庫中的表和視圖的個數以及名稱；

◆某個表或者視圖中列的個數以及每一列的名稱、數據類型、長度、精度、描述等；

◆某個表上定義的約束；

◆某個表上定義的索引以及主鍵/外鍵的信息。

下面我們將介紹幾種獲取元數據的方法。

獲取元數據

使用系統存儲過程與系統函數訪問元數據

獲取元數據最常用的方法是使用 SQL Server 提供的系統存儲過程與系統函數。

系統存儲過程與系統函數在系統表和元數據之間提供了一個抽象層，使得我們不用直接查詢系統表就能獲得當前數據庫對象的元數據。

常用的與元數據有關的系統存儲過程有以下一些：

系統存儲過程

◆sp_columns 返回指定表或視圖的列的詳細信息。

◆sp_databases 返回當前服務器上的所有數據庫的基本信息。

◆sp_fkeys 若參數為帶有主鍵的表，則返回包含指向該表的外鍵的所有表；若參數為帶有外鍵的表名，則返回所有同過主鍵/外鍵關系與該外鍵相關聯的所有表。

◆sp_pkeys 返回指定表的主鍵信息。

◆sp_server_info 返回當前服務器的各種特性及其對應取值。

◆sp_sproc_columns 返回指定存儲過程的的輸入、輸出參數的信息。

◆sp_statistics 返回指定的表或索引視圖上的所有索引以及統計的信息。

◆sp_stored_procedures 返回當前數據庫的存儲過程列表，包含系統存儲過程。

◆sp_tables 返回當前數據庫的所有表和視圖，包含系統表。

常用的與元數據有關的系統函數有以下一些：

系統函數

◆COLUMNPROPERTY 返回有關列或過程參數的信息，如是否允許空值，是否為計算列等。

◆COL_LENGTH 返回指定數據庫的指定屬性值，如是否處于只讀模式等。

◆DATABASEPROPERTYEX 返回指定數據庫的指定選項或屬性的當前設置，如數據庫的狀態、恢復模型等。

◆OBJECT_ID 返回指定數據庫對象名的標識號

◆OBJECT_NAME 返回指定數據庫對象標識號的對象名。

◆OBJECTPROPERTY 返回指定數據庫對象標識號的有關信息，如是否為表，是否為約束等。

◆fn_listextendedproperty 返回數據庫對象的擴展屬性值，如對象描述、格式規則、輸入掩碼等。

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

例如，如果要獲得當前服務器上所有數據庫的基本信息，我們可以在查詢分析器里面運行：

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 標準的數據庫系統，使用信息架構視圖總是可以正常工作的。

信息架構視圖

◆INFORMATION_SCHEMA.CHECK_CONSTRAINTS：返回有關列或過程參數的信息，如是否允許空值，是否為計算列等。

◆INFORMATION_SCHEMA.COLUMNS：返回當前數據庫中當前用戶可以訪問的所有列及其基本信息。

◆INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE：返回當前數據庫中定義了約束的所有列及其約束名。

◆INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE：返回當前數據庫中定義了約束的所有表及其約束名。

◆INFORMATION_SCHEMA.KEY_COLUMN_USAGE：返回當前數據庫中作為主鍵/外鍵約束的所有列。

◆INFORMATION_SCHEMA.SCHEMATA：返回當前用戶具有權限的所有數據庫及其基本信息。

◆INFORMATION_SCHEMA.TABLES：返回當前用戶具有權限的當前數據庫中的所有表或者視圖及其基本信息。