獲取SQL Server元數(shù)據(jù)的方法與配置
此文章主要介紹的是獲取SQL Server元數(shù)據(jù)的方法配置的實際操作步驟，獲取SQL Server元數(shù)據(jù)的方法在實際操作是經常被用到了，所以你對其有更多的了解還是好的，以下就是文章的主要內容的詳細描述，望大家在瀏覽之后會對其有更深的了解。
元數(shù)據(jù)簡介
元數(shù)據(jù) (metadata) 最常見的定義為“有關數(shù)據(jù)的結構數(shù)據(jù)”，或者再簡單一點就是“關于數(shù)據(jù)的信息”，日常生活中的圖例、圖書館目錄卡和名片等都可以看作是元數(shù)據(jù)。在關系型數(shù)據(jù)庫管理系統(tǒng) (DBMS) 中，SQL Server元數(shù)據(jù)描述了數(shù)據(jù)的結構和意義。比如在管理、維護 SQL Server 或者是開發(fā)數(shù)據(jù)庫應用程序的時候，我們經常要獲取一些涉及到數(shù)據(jù)庫架構的信息：
某個數(shù)據(jù)庫中的表和視圖的個數(shù)以及名稱；
某個表或者視圖中列的個數(shù)以及每一列的名稱、數(shù)據(jù)類型、長度、精度、描述等；
某個表上定義的約束；
某個表上定義的索引以及主鍵/外鍵的信息。
下面我們將介紹幾種獲取SQL Server元數(shù)據(jù)的方法。
獲取元數(shù)據(jù)
使用系統(tǒng)存儲過程與系統(tǒng)函數(shù)訪問元數(shù)據(jù)
獲取元數(shù)據(jù)最常用的方法是使用 SQL Server 提供的系統(tǒng)存儲過程與系統(tǒng)函數(shù)。
系統(tǒng)存儲過程與系統(tǒng)函數(shù)在系統(tǒng)表和SQL Server元數(shù)據(jù)之間提供了一個抽象層，使得我們不用直接查詢系統(tǒng)表就能獲得當前數(shù)據(jù)庫對象的元數(shù)據(jù)。
常用的與元數(shù)據(jù)有關的系統(tǒng)存儲過程有以下一些：
系統(tǒng)存儲過程
sp_columns 返回指定表或視圖的列的詳細信息。
sp_databases 返回當前服務器上的所有數(shù)據(jù)庫的基本信息。
sp_fkeys 若參數(shù)為帶有主鍵的表，則返回包含指向該表的外鍵的所有表；若參數(shù)為帶有外鍵的表名，則返回所有同過主鍵/外鍵關系與該外鍵相關聯(lián)的所有表。
sp_pkeys 返回指定表的主鍵信息。
sp_server_info 返回當前服務器的各種特性及其對應取值。
sp_sproc_columns 返回指定存儲過程的的輸入、輸出參數(shù)的信息。
sp_statistics 返回指定的表或索引視圖上的所有索引以及統(tǒng)計的信息。
sp_stored_procedures 返回當前數(shù)據(jù)庫的存儲過程列表，包含系統(tǒng)存儲過程。
sp_tables 返回當前數(shù)據(jù)庫的所有表和視圖，包含系統(tǒng)表。
常用的與SQL Server元數(shù)據(jù)有關的系統(tǒng)函數(shù)有以下一些：
系統(tǒng)函數(shù)
COLUMNPROPERTY 返回有關列或過程參數(shù)的信息，如是否允許空值，是否為計算列等。
COL_LENGTH 返回指定數(shù)據(jù)庫的指定屬性值，如是否處于只讀模式等。
DATABASEPROPERTYEX 返回指定數(shù)據(jù)庫的指定選項或屬性的當前設置，如數(shù)據(jù)庫的狀態(tài)、恢復模型等。
OBJECT_ID 返回指定數(shù)據(jù)庫對象名的標識號
OBJECT_NAME 返回指定數(shù)據(jù)庫對象標識號的對象名。
OBJECTPROPERTY 返回指定數(shù)據(jù)庫對象標識號的有關信息，如是否為表，是否為約束等。
fn_listextendedproperty 返回數(shù)據(jù)庫對象的擴展屬性值，如對象描述、格式規(guī)則、輸入掩碼等。
由于我們無法直接利用到存儲過程與函數(shù)的返回結果，因此只有在我們關心的只是查詢的結果，而不需要進一步利用這些結果的時候，我們會使用系統(tǒng)存儲過程與系統(tǒng)函數(shù)來查詢SQL Server元數(shù)據(jù)。
例如，如果要獲得當前服務器上所有數(shù)據(jù)庫的基本信息，我們可以在查詢分析器里面運行：
EXEC sp_databases GO
在返回結果中我們可以看到數(shù)據(jù)庫的名稱、大小及備注等信息。
但是如果要引用這部分信息，或者存儲這部分信息以供后面使用，那么我們必須借助中間表來完成這個操作：
CREATE TABLE #sp_result ( DATABASE_NAME sysname, DATABASE_SIZE int, REMARKS varchar(254) NULL ) GO INSERT INTO #sp_result EXEC ('sp_databases') GO
使用信息架構視圖訪問元數(shù)據(jù)
信息架構視圖基于 SQL-92 標準中針對架構視圖的定義，這些視圖獨立于系統(tǒng)表，提供了關于 SQL Server 元數(shù)據(jù)的內部視圖。信息架構視圖的***優(yōu)點是，即使我們對系統(tǒng)表進行了重要的修改，應用程序也可以正常地使用這些視圖進行訪問。因此對于應用程序來說，只要是符合 SQL-92 標準的數(shù)據(jù)庫系統(tǒng)，使用信息架構視圖總是可以正常工作的。
信息架構視圖
INFORMATION_SCHEMA.CHECK_CONSTRAINTS：返回有關列或過程參數(shù)的信息，如是否允許空值，是否為計算列等。
INFORMATION_SCHEMA.COLUMNS：返回當前數(shù)據(jù)庫中當前用戶可以訪問的所有列及其基本信息。
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE：返回當前數(shù)據(jù)庫中定義了約束的所有列及其約束名。
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE：返回當前數(shù)據(jù)庫中定義了約束的所有表及其約束名。
INFORMATION_SCHEMA.KEY_COLUMN_USAGE：返回當前數(shù)據(jù)庫中作為主鍵/外鍵約束的所有列。
INFORMATION_SCHEMA.SCHEMATA：返回當前用戶具有權限的所有數(shù)據(jù)庫及其基本信息。
INFORMATION_SCHEMA.TABLES：返回當前用戶具有權限的當前數(shù)據(jù)庫中的所有表或者視圖及其基本信息。
INFORMATION_SCHEMA.VIEWS：返回當前數(shù)據(jù)庫中的當前用戶可以訪問的視圖及其所有者、定義等信息。
由于這些信息架構都是以視圖的方式存在的，因此我們可以很方便地獲得并利用需要的信息。
例如，我們要得到某個表有多少列，可以使用以下語句：
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='mytable'
使用系統(tǒng)表訪問元數(shù)據(jù)
雖然使用系統(tǒng)存儲過程、系統(tǒng)函數(shù)與信息架構視圖已經可以為我們提供了相當豐富的元數(shù)據(jù)信息，但是對于某些特殊的元數(shù)據(jù)信息，我們仍然需要直接對系統(tǒng)表進行查詢。因為SQL Server 將所有數(shù)據(jù)庫對象的信息均存放在系統(tǒng)表中，作為 SQL Server 的管理、開發(fā)人員，了解各個系統(tǒng)表的作用將有助于我們了解 SQL Server 的內在工作原理。
SQL Server 的系統(tǒng)表非常多，其中最常用的與SQL Server元數(shù)據(jù)查詢有關的表有如下一些：
syscolumns 存儲每個表和視圖中的每一列的信息以及存儲過程中的每個參數(shù)的信息。
syscomments 存儲包含每個視圖、規(guī)則、默認值、觸發(fā)器、CHECK 約束、DEFAULT 約束和存儲過程的原始 SQL 文本語句。
sysconstraints 存儲當前數(shù)據(jù)庫中每一個約束的基本信息。
sysdatabases 存儲當前服務器上每一個數(shù)據(jù)庫的基本信息。
sysindexes 存儲當前數(shù)據(jù)庫中的每個索引的信息。
sysobjects 存儲數(shù)據(jù)庫內的每個對象（約束、默認值、日志、規(guī)則、存儲過程等）的基本信息。
sysreferences 存儲所有包括 FOREIGN KEY 約束的列。
systypes 存儲系統(tǒng)提供的每種數(shù)據(jù)類型和用戶定義數(shù)據(jù)類型的詳細信息。
將系統(tǒng)存儲過程、系統(tǒng)函數(shù)、信息架構視圖與系統(tǒng)表結合使用，可以方便地讓我們獲得所有需要的SQL Server元數(shù)據(jù)信息。
示例：
1、 獲得當前數(shù)據(jù)庫所有用戶表的名稱。
SELECT OBJECT_NAME (id) FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
其中主要用到了系統(tǒng)表 sysobjects以及其屬性 xtype，還有就是用到了 OBJECTPROPERTY 系統(tǒng)函數(shù)來判斷是不是安裝 SQL Server 的過程中創(chuàng)建的對象。
2、獲得指定表上所有的索引名稱。
SELECT name FROM sysindexes WHERE id = OBJECT_ID ('mytable') AND indid > 0
綜合實例
下面給出了一個存儲過程，它的作用是自動將當前數(shù)據(jù)庫的用戶存儲過程加密。
- 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 上通過。
【編輯推薦】
