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