如何擷取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

)

insert into #sp_result 

exec ('sp_databases')

使用資訊架構視圖通路中繼資料 

資訊架構視圖基于 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：傳回目前使用者具有權限的目前資料庫中的所有表或者視圖及其基本資訊。

◆information_schema.views：傳回目前資料庫中的目前使用者可以通路的視圖及其所有者、定義等資訊。

由于這些資訊架構都是以視圖的方式存在的，是以我們可以很友善地獲得并利用需要的資訊。

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

select count(*) from information_schema.columns

where table_name='mytable'

使用系統表通路中繼資料

雖然使用系統存儲過程、系統函數與資訊架構視圖已經可以為我們提供了相當豐富的中繼資料資訊，但是對于某些特殊的中繼資料資訊，我們仍然需要直接對系統表進行查詢。因為sql server 将所有資料庫對象的資訊均存放在系統表中，作為 sql server 的管理、開發人員，了解各個系統表的作用将有助于我們了解 sql server 的内在工作原理。

sql server 的系統表非常多，其中最常用的與中繼資料查詢有關的表有如下一些：

syscolumns 存儲每個表和視圖中的每一列的資訊以及存儲過程中的每個參數的資訊。

syscomments 存儲包含每個視圖、規則、預設值、觸發器、check 限制、default 限制和存儲過程的原始 sql 文本語句。

sysconstraints 存儲目前資料庫中每一個限制的基本資訊。

sysdatabases 存儲目前伺服器上每一個資料庫的基本資訊。

sysindexes 存儲目前資料庫中的每個索引的資訊。

sysobjects 存儲資料庫内的每個對象（限制、預設值、日志、規則、存儲過程等）的基本資訊。

sysreferences 存儲所有包括 foreign key 限制的列。

systypes 存儲系統提供的每種資料類型和使用者定義資料類型的詳細資訊。

将系統存儲過程、系統函數、資訊架構視圖與系統表結合使用，可以友善地讓我們獲得所有需要的中繼資料資訊。

示例：

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)

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

end

close sp_cursor 

deallocate sp_cursor

該存儲過程利用了 sysobjects 和 syscomments 表，并巧妙地修改了原存儲過程的 sql 定義語句，将 as 修改為了 with encryption as，進而達到了加密存儲過程的目的。本存儲過程在 sql server 2000 上通過。

歡迎加群互相學習，共同進步。qq群：ios: 58099570 | android: 330987132 | go：217696290 | python：336880185 | 做人要厚道，轉載請注明出處！http://www.cnblogs.com/sunshine-anycall/archive/2009/09/17/1568874.html