訪問 Microsoft SQL Server 元數(shù)據(jù)的三種方法
2024-08-31 00:48:06
供稿：網(wǎng)友
訪問 microsoft sql server 元數(shù)據(jù)的三種方法
上海微創(chuàng)軟件有限公司 肖桂東
適用讀者：microsoft sql server 中、高級(jí)用戶
元數(shù)據(jù)簡介
元數(shù)據(jù) (metadata) 最常見的定義為"有關(guān)數(shù)據(jù)的結(jié)構(gòu)數(shù)據(jù)"，或者再簡單一點(diǎn)就是"關(guān)于數(shù)據(jù)的信息"，日常生活中的圖例、圖書館目錄卡和名片等都可以看作是元數(shù)據(jù)。在關(guān)系型數(shù)據(jù)庫管理系統(tǒng) (dbms) 中，元數(shù)據(jù)描述了數(shù)據(jù)的結(jié)構(gòu)和意義。比如在管理、維護(hù) sql server 或者是開發(fā)數(shù)據(jù)庫應(yīng)用程序的時(shí)候，我們經(jīng)常要獲取一些涉及到數(shù)據(jù)庫架構(gòu)的信息：
某個(gè)數(shù)據(jù)庫中的表和視圖的個(gè)數(shù)以及名稱 ；
某個(gè)表或者視圖中列的個(gè)數(shù)以及每一列的名稱、數(shù)據(jù)類型、長度、精度、描述等；
某個(gè)表上定義的約束；
某個(gè)表上定義的索引以及主鍵/外鍵的信息。
下面我們將介紹幾種獲取元數(shù)據(jù)的方法。
獲取元數(shù)據(jù)
使用系統(tǒng)存儲(chǔ)過程與系統(tǒng)函數(shù)訪問元數(shù)據(jù)
獲取元數(shù)據(jù)最常用的方法是使用 sql server 提供的系統(tǒng)存儲(chǔ)過程與系統(tǒng)函數(shù)。
系統(tǒng)存儲(chǔ)過程與系統(tǒng)函數(shù)在系統(tǒng)表和元數(shù)據(jù)之間提供了一個(gè)抽象層，使得我們不用直接查詢系統(tǒng)表就能獲得當(dāng)前數(shù)據(jù)庫對(duì)象的元數(shù)據(jù)。
常用的與元數(shù)據(jù)有關(guān)的系統(tǒng)存儲(chǔ)過程有以下一些：
系統(tǒng)存儲(chǔ)過程描述sp_columns返回指定表或視圖的列的詳細(xì)信息。sp_databases返回當(dāng)前服務(wù)器上的所有數(shù)據(jù)庫的基本信息。sp_fkeys若參數(shù)為帶有主鍵的表，則返回包含指向該表的外鍵的所有表；若參數(shù)為帶有外鍵的表名，則返回所有同過主鍵/外鍵關(guān)系與該外鍵相關(guān)聯(lián)的所有表。sp_pkeys返回指定表的主鍵信息。sp_server_info返回當(dāng)前服務(wù)器的各種特性及其對(duì)應(yīng)取值。sp_sproc_columns返回指定存儲(chǔ)過程的的輸入、輸出參數(shù)的信息。sp_statistics返回指定的表或索引視圖上的所有索引以及統(tǒng)計(jì)的信息。sp_stored_procedures返回當(dāng)前數(shù)據(jù)庫的存儲(chǔ)過程列表，包含系統(tǒng)存儲(chǔ)過程。sp_tables返回當(dāng)前數(shù)據(jù)庫的所有表和視圖，包含系統(tǒng)表。
常用的與元數(shù)據(jù)有關(guān)的系統(tǒng)函數(shù)有以下一些：
系統(tǒng)函數(shù)描述columnproperty返回有關(guān)列或過程參數(shù)的信息，如是否允許空值，是否為計(jì)算列等。col_length返回指定數(shù)據(jù)庫的指定屬性值，如是否處于只讀模式等。databasepropertyex返回指定數(shù)據(jù)庫的指定選項(xiàng)或?qū)傩缘漠?dāng)前設(shè)置，如數(shù)據(jù)庫的狀態(tài)、恢復(fù)模型等。object_id返回指定數(shù)據(jù)庫對(duì)象名的標(biāo)識(shí)號(hào)object_name返回指定數(shù)據(jù)庫對(duì)象標(biāo)識(shí)號(hào)的對(duì)象名。objectproperty返回指定數(shù)據(jù)庫對(duì)象標(biāo)識(shí)號(hào)的有關(guān)信息，如是否為表，是否為約束等。fn_listextendedproperty返回?cái)?shù)據(jù)庫對(duì)象的擴(kuò)展屬性值，如對(duì)象描述、格式規(guī)則、輸入掩碼等。
由于我們無法直接利用到存儲(chǔ)過程與函數(shù)的返回結(jié)果，因此只有在我們關(guān)心的只是查詢的結(jié)果，而不需要進(jìn)一步利用這些結(jié)果的時(shí)候，我們會(huì)使用系統(tǒng)存儲(chǔ)過程與系統(tǒng)函數(shù)來查詢?cè)獢?shù)據(jù)。
例如，如果要獲得當(dāng)前服務(wù)器上所有數(shù)據(jù)庫的基本信息，我們可以在查詢分析器里面運(yùn)行：
exec sp_databases
go
在返回結(jié)果中我們可以看到數(shù)據(jù)庫的名稱、大小及備注等信息。
但是如果要引用這部分信息，或者存儲(chǔ)這部分信息以供后面使用，那么我們必須借助中間表來完成這個(gè)操作：
create table #sp_result
(
database_name sysname,
database_size int,
remarks varchar(254) null
)
go
insert into #sp_result
exec ('sp_databases')
go
使用信息架構(gòu)視圖訪問元數(shù)據(jù)
信息架構(gòu)視圖基于 sql-92 標(biāo)準(zhǔn)中針對(duì)架構(gòu)視圖的定義，這些視圖獨(dú)立于系統(tǒng)表，提供了關(guān)于 sql server 元數(shù)據(jù)的內(nèi)部視圖。信息架構(gòu)視圖的最大優(yōu)點(diǎn)是，即使我們對(duì)系統(tǒng)表進(jìn)行了重要的修改，應(yīng)用程序也可以正常地使用這些視圖進(jìn)行訪問。因此對(duì)于應(yīng)用程序來說，只要是符合 sql-92 標(biāo)準(zhǔn)的數(shù)據(jù)庫系統(tǒng)，使用信息架構(gòu)視圖總是可以正常工作的。
常用的信息架構(gòu)視圖有以下一些：
信息架構(gòu)視圖描述information_schema .check_constraints返回有關(guān)列或過程參數(shù)的信息，如是否允許空值，是否為計(jì)算列等。information_schema .columns返回當(dāng)前數(shù)據(jù)庫中當(dāng)前用戶可以訪問的所有列及其基本信息。information_schema .constraint_column_usage返回當(dāng)前數(shù)據(jù)庫中定義了約束的所有列及其約束名。information_schema .constraint_table_usage返回當(dāng)前數(shù)據(jù)庫中定義了約束的所有表及其約束名。information_schema .key_column_usage返回當(dāng)前數(shù)據(jù)庫中作為主鍵/外鍵約束的所有列。information_schema .schemata返回當(dāng)前用戶具有權(quán)限的所有數(shù)據(jù)庫及其基本信息。information_schema .tables返回當(dāng)前用戶具有權(quán)限的當(dāng)前數(shù)據(jù)庫中的所有表或者視圖及其基本信息。information_schema .views返回當(dāng)前數(shù)據(jù)庫中的當(dāng)前用戶可以訪問的視圖及其所有者、定義等信息。
由于這些信息架構(gòu)都是以視圖的方式存在的，因此我們可以很方便地獲得并利用需要的信息。
例如，我們要得到某個(gè)表有多少列，可以使用以下語句：
select count(*) from information_schema.columns
where table_name='mytable'
使用系統(tǒng)表訪問元數(shù)據(jù)
雖然使用系統(tǒng)存儲(chǔ)過程、系統(tǒng)函數(shù)與信息架構(gòu)視圖已經(jīng)可以為我們提供了相當(dāng)豐富的元數(shù)據(jù)信息，但是對(duì)于某些特殊的元數(shù)據(jù)信息，我們?nèi)匀恍枰苯訉?duì)系統(tǒng)表進(jìn)行查詢。因?yàn)閟ql server 將所有數(shù)據(jù)庫對(duì)象的信息均存放在系統(tǒng)表中，作為 sql server 的管理、開發(fā)人員，了解各個(gè)系統(tǒng)表的作用將有助于我們了解 sql server 的內(nèi)在工作原理。
sql server 的系統(tǒng)表非常多，其中最常用的與元數(shù)據(jù)查詢有關(guān)的表有如下一些：
系統(tǒng)表描述syscolumns存儲(chǔ)每個(gè)表和視圖中的每一列的信息以及存儲(chǔ)過程中的每個(gè)參數(shù)的信息。syscomments存儲(chǔ)包含每個(gè)視圖、規(guī)則、默認(rèn)值、觸發(fā)器、check 約束、default 約束和存儲(chǔ)過程的原始 sql 文本語句。sysconstraints存儲(chǔ)當(dāng)前數(shù)據(jù)庫中每一個(gè)約束的基本信息。sysdatabases存儲(chǔ)當(dāng)前服務(wù)器上每一個(gè)數(shù)據(jù)庫的基本信息。sysindexes存儲(chǔ)當(dāng)前數(shù)據(jù)庫中的每個(gè)索引的信息。sysobjects存儲(chǔ)數(shù)據(jù)庫內(nèi)的每個(gè)對(duì)象（約束、默認(rèn)值、日志、規(guī)則、存儲(chǔ)過程等）的基本信息。sysreferences存儲(chǔ)所有包括 foreign key 約束的列。systypes存儲(chǔ)系統(tǒng)提供的每種數(shù)據(jù)類型和用戶定義數(shù)據(jù)類型的詳細(xì)信息。
將系統(tǒng)存儲(chǔ)過程、系統(tǒng)函數(shù)、信息架構(gòu)視圖與系統(tǒng)表結(jié)合使用，可以方便地讓我們獲得所有需要的元數(shù)據(jù)信息。
示例：
1、 獲得當(dāng)前數(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)建的對(duì)象。
2、 獲得指定表上所有的索引名稱
select name from sysindexes
where id = object_id ('mytable') and indid > 0
綜合實(shí)例
下面給出了一個(gè)存儲(chǔ)過程，它的作用是自動(dòng)將當(dāng)前數(shù)據(jù)庫的用戶存儲(chǔ)過程加密。
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
該存儲(chǔ)過程利用了 sysobjects 和 syscomments 表，并巧妙地修改了原存儲(chǔ)過程的 sql 定義語句，將 as 修改為了 with encryption as，從而達(dá)到了加密存儲(chǔ)過程的目的。本存儲(chǔ)過程在 sql server 2000 上通過。
