主頁(yè) > 知識(shí)庫(kù) > 獲取SQL Server數(shù)據(jù)庫(kù)元數(shù)據(jù)的幾種方法

獲取SQL Server數(shù)據(jù)庫(kù)元數(shù)據(jù)的幾種方法

熱門(mén)標(biāo)簽:電銷機(jī)器人被曝光 地圖標(biāo)注一個(gè)圓圈怎么用 洛陽(yáng)外呼系統(tǒng)平臺(tái) 廣州人工電銷機(jī)器人費(fèi)用 怎樣把地圖標(biāo)注導(dǎo)入公司地址 寧波人工外呼系統(tǒng)有效果嗎 如何在地圖標(biāo)注自己店鋪 真人語(yǔ)音電銷機(jī)器人 400外呼系統(tǒng)合法

元數(shù)據(jù)簡(jiǎn)介

元數(shù)據(jù) (metadata) 最常見(jiàn)的定義為“有關(guān)數(shù)據(jù)的結(jié)構(gòu)數(shù)據(jù)”,或者再簡(jiǎn)單一點(diǎn)就是“關(guān)于數(shù)據(jù)的信息”,日常生活中的圖例、圖書(shū)館目錄卡和名片等都可以看作是元數(shù)據(jù)。在關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng) (DBMS) 中,元數(shù)據(jù)描述了數(shù)據(jù)的結(jié)構(gòu)和意義。比如在管理、維護(hù) SQL Server 或者是開(kāi)發(fā)數(shù)據(jù)庫(kù)應(yīng)用程序的時(shí)候,我們經(jīng)常要獲取一些涉及到數(shù)據(jù)庫(kù)架構(gòu)的信息:

◆某個(gè)數(shù)據(jù)庫(kù)中的表和視圖的個(gè)數(shù)以及名稱;
◆某個(gè)表或者視圖中列的個(gè)數(shù)以及每一列的名稱、數(shù)據(jù)類型、長(zhǎng)度、精度、描述等;
◆某個(gè)表上定義的約束;
◆某個(gè)表上定義的索引以及主鍵/外鍵的信息。

下面我們將介紹幾種獲取元數(shù)據(jù)的方法。

獲取元數(shù)據(jù)

使用系統(tǒng)存儲(chǔ)過(guò)程與系統(tǒng)函數(shù)訪問(wèn)元數(shù)據(jù)

獲取元數(shù)據(jù)最常用的方法是使用 SQL Server 提供的系統(tǒng)存儲(chǔ)過(guò)程與系統(tǒng)函數(shù)。

系統(tǒng)存儲(chǔ)過(guò)程與系統(tǒng)函數(shù)在系統(tǒng)表和元數(shù)據(jù)之間提供了一個(gè)抽象層,使得我們不用直接查詢系統(tǒng)表就能獲得當(dāng)前數(shù)據(jù)庫(kù)對(duì)象的元數(shù)據(jù)。

常用的與元數(shù)據(jù)有關(guān)的系統(tǒng)存儲(chǔ)過(guò)程有以下一些:

系統(tǒng)存儲(chǔ)過(guò)程

◆sp_columns 返回指定表或視圖的列的詳細(xì)信息。
◆sp_databases 返回當(dāng)前服務(wù)器上的所有數(shù)據(jù)庫(kù)的基本信息。
◆sp_fkeys 若參數(shù)為帶有主鍵的表,則返回包含指向該表的外鍵的所有表;若參數(shù)為帶有外鍵的表名,則返回所有同過(guò)主鍵/外鍵關(guān)系與該外鍵相關(guān)聯(lián)的所有表。
◆sp_pkeys 返回指定表的主鍵信息。
◆sp_server_info 返回當(dāng)前服務(wù)器的各種特性及其對(duì)應(yīng)取值。
◆sp_sproc_columns 返回指定存儲(chǔ)過(guò)程的的輸入、輸出參數(shù)的信息。
◆sp_statistics 返回指定的表或索引視圖上的所有索引以及統(tǒng)計(jì)的信息。
◆sp_stored_procedures 返回當(dāng)前數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程列表,包含系統(tǒng)存儲(chǔ)過(guò)程。
◆sp_tables 返回當(dāng)前數(shù)據(jù)庫(kù)的所有表和視圖,包含系統(tǒng)表。

常用的與元數(shù)據(jù)有關(guān)的系統(tǒng)函數(shù)有以下一些:

系統(tǒng)函數(shù)

◆COLUMNPROPERTY 返回有關(guān)列或過(guò)程參數(shù)的信息,如是否允許空值,是否為計(jì)算列等。
◆COL_LENGTH 返回指定數(shù)據(jù)庫(kù)的指定屬性值,如是否處于只讀模式等。
◆DATABASEPROPERTYEX 返回指定數(shù)據(jù)庫(kù)的指定選項(xiàng)或?qū)傩缘漠?dāng)前設(shè)置,如數(shù)據(jù)庫(kù)的狀態(tài)、恢復(fù)模型等。
◆OBJECT_ID 返回指定數(shù)據(jù)庫(kù)對(duì)象名的標(biāo)識(shí)號(hào)
◆OBJECT_NAME 返回指定數(shù)據(jù)庫(kù)對(duì)象標(biāo)識(shí)號(hào)的對(duì)象名。
◆OBJECTPROPERTY 返回指定數(shù)據(jù)庫(kù)對(duì)象標(biāo)識(shí)號(hào)的有關(guān)信息,如是否為表,是否為約束等。
◆fn_listextendedproperty 返回?cái)?shù)據(jù)庫(kù)對(duì)象的擴(kuò)展屬性值,如對(duì)象描述、格式規(guī)則、輸入掩碼等。

由于我們無(wú)法直接利用到存儲(chǔ)過(guò)程與函數(shù)的返回結(jié)果,因此只有在我們關(guān)心的只是查詢的結(jié)果,而不需要進(jìn)一步利用這些結(jié)果的時(shí)候,我們會(huì)使用系統(tǒng)存儲(chǔ)過(guò)程與系統(tǒng)函數(shù)來(lái)查詢?cè)獢?shù)據(jù)。

例如,如果要獲得當(dāng)前服務(wù)器上所有數(shù)據(jù)庫(kù)的基本信息,我們可以在查詢分析器里面運(yùn)行:

EXEC sp_databases
GO

在返回結(jié)果中我們可以看到數(shù)據(jù)庫(kù)的名稱、大小及備注等信息。

但是如果要引用這部分信息,或者存儲(chǔ)這部分信息以供后面使用,那么我們必須借助中間表來(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

使用信息架構(gòu)視圖訪問(wèn)元數(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)行訪問(wèn)。因此對(duì)于應(yīng)用程序來(lái)說(shuō),只要是符合 SQL-92 標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)系統(tǒng),使用信息架構(gòu)視圖總是可以正常工作的。

信息架構(gòu)視圖

◆INFORMATION_SCHEMA.CHECK_CONSTRAINTS:返回有關(guān)列或過(guò)程參數(shù)的信息,如是否允許空值,是否為計(jì)算列等。
◆INFORMATION_SCHEMA.COLUMNS:返回當(dāng)前數(shù)據(jù)庫(kù)中當(dāng)前用戶可以訪問(wèn)的所有列及其基本信息。
◆INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE:返回當(dāng)前數(shù)據(jù)庫(kù)中定義了約束的所有列及其約束名。
◆INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE:返回當(dāng)前數(shù)據(jù)庫(kù)中定義了約束的所有表及其約束名。
◆INFORMATION_SCHEMA.KEY_COLUMN_USAGE:返回當(dāng)前數(shù)據(jù)庫(kù)中作為主鍵/外鍵約束的所有列。
◆INFORMATION_SCHEMA.SCHEMATA:返回當(dāng)前用戶具有權(quán)限的所有數(shù)據(jù)庫(kù)及其基本信息。
◆INFORMATION_SCHEMA.TABLES:返回當(dāng)前用戶具有權(quán)限的當(dāng)前數(shù)據(jù)庫(kù)中的所有表或者視圖及其基本信息。
◆INFORMATION_SCHEMA.VIEWS:返回當(dāng)前數(shù)據(jù)庫(kù)中的當(dāng)前用戶可以訪問(wèn)的視圖及其所有者、定義等信息。

由于這些信息架構(gòu)都是以視圖的方式存在的,因此我們可以很方便地獲得并利用需要的信息。

例如,我們要得到某個(gè)表有多少列,可以使用以下語(yǔ)句:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME='mytable'

使用系統(tǒng)表訪問(wèn)元數(shù)據(jù)

雖然使用系統(tǒng)存儲(chǔ)過(guò)程、系統(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ù)庫(kù)對(duì)象的信息均存放在系統(tǒng)表中,作為 SQL Server 的管理、開(kāi)發(fā)人員,了解各個(gè)系統(tǒng)表的作用將有助于我們了解 SQL Server 的內(nèi)在工作原理。

SQL Server 的系統(tǒng)表非常多,其中最常用的與元數(shù)據(jù)查詢有關(guān)的表有如下一些:
syscolumns 存儲(chǔ)每個(gè)表和視圖中的每一列的信息以及存儲(chǔ)過(guò)程中的每個(gè)參數(shù)的信息。
syscomments 存儲(chǔ)包含每個(gè)視圖、規(guī)則、默認(rèn)值、觸發(fā)器、CHECK 約束、DEFAULT 約束和存儲(chǔ)過(guò)程的原始 SQL 文本語(yǔ)句。
sysconstraints 存儲(chǔ)當(dāng)前數(shù)據(jù)庫(kù)中每一個(gè)約束的基本信息。
sysdatabases 存儲(chǔ)當(dāng)前服務(wù)器上每一個(gè)數(shù)據(jù)庫(kù)的基本信息。
sysindexes 存儲(chǔ)當(dāng)前數(shù)據(jù)庫(kù)中的每個(gè)索引的信息。
sysobjects 存儲(chǔ)數(shù)據(jù)庫(kù)內(nèi)的每個(gè)對(duì)象(約束、默認(rèn)值、日志、規(guī)則、存儲(chǔ)過(guò)程等)的基本信息。
sysreferences 存儲(chǔ)所有包括 FOREIGN KEY 約束的列。
systypes 存儲(chǔ)系統(tǒng)提供的每種數(shù)據(jù)類型和用戶定義數(shù)據(jù)類型的詳細(xì)信息。

將系統(tǒng)存儲(chǔ)過(guò)程、系統(tǒng)函數(shù)、信息架構(gòu)視圖與系統(tǒng)表結(jié)合使用,可以方便地讓我們獲得所有需要的元數(shù)據(jù)信息。

示例:

1、 獲得當(dāng)前數(shù)據(jù)庫(kù)所有用戶表的名稱。

SELECT OBJECT_NAME (id)
FROM sysobjects
WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0

其中主要用到了系統(tǒng)表 sysobjects以及其屬性 xtype,還有就是用到了 OBJECTPROPERTY 系統(tǒng)函數(shù)來(lái)判斷是不是安裝 SQL Server 的過(guò)程中創(chuàng)建的對(duì)象。

2、獲得指定表上所有的索引名稱。

SELECT name FROM sysindexes
WHERE id = OBJECT_ID ('mytable') AND indid > 0

綜合實(shí)例

下面給出了一個(gè)存儲(chǔ)過(guò)程,它的作用是自動(dòng)將當(dāng)前數(shù)據(jù)庫(kù)的用戶存儲(chǔ)過(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

該存儲(chǔ)過(guò)程利用了 sysobjects 和 syscomments 表,并巧妙地修改了原存儲(chǔ)過(guò)程的 SQL 定義語(yǔ)句,將 AS 修改為了 WITH ENCRYPTION AS,從而達(dá)到了加密存儲(chǔ)過(guò)程的目的。本存儲(chǔ)過(guò)程在 SQL Server 2000 上通過(guò)。

您可能感興趣的文章:
  • 調(diào)用MySQL中數(shù)據(jù)庫(kù)元數(shù)據(jù)的方法
  • SQL Server 使用 SET FMTONLY ON 獲得表的元數(shù)據(jù)

標(biāo)簽:南昌 煙臺(tái) 北海 東營(yíng) 晉中 珠海 咸寧 石家莊

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《獲取SQL Server數(shù)據(jù)庫(kù)元數(shù)據(jù)的幾種方法》,本文關(guān)鍵詞  獲取,SQL,Server,數(shù)據(jù)庫(kù),元,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《獲取SQL Server數(shù)據(jù)庫(kù)元數(shù)據(jù)的幾種方法》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于獲取SQL Server數(shù)據(jù)庫(kù)元數(shù)據(jù)的幾種方法的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章