---- 系統(tǒng)表 --------------------------------------------------------------------------------------
雖然使用系統(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)行查詢(xún)。因?yàn)? SQL 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ù)查詢(xún)有關(guān)的表有如下一些：
?
|
系統(tǒng)表 |
描述 |
|
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ù)類(lèi)型和用戶(hù)定義數(shù)據(jù)類(lèi)型的詳細(xì)信息。 |
將系統(tǒng)存儲(chǔ)過(guò)程、系統(tǒng)函數(shù)、信息架構(gòu)視圖與系統(tǒng)表結(jié)合使用，可以方便地讓我們獲得所有需要的元數(shù)據(jù)信息。
示例：
1 、 獲得當(dāng)前數(shù)據(jù)庫(kù)所有用戶(hù)表的名稱(chēng)。
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 、 獲得指定表上所有的索引名稱(chēng)
SELECT name FROM sysindexes
WHERE id = OBJECT_ID ('mytable') AND indid > 0
---- 系統(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)表。 |
---- 系統(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ī)則、輸入掩碼等。 |
---- 使用信息架構(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)視圖有以下一些：
|
信息架構(gòu)視圖 |
描述 |
|
INFORMATION_SCHEMA .CHECK_CONSTRAINTS |
返回有關(guān)列或過(guò)程參數(shù)的信息，如是否允許空值，是否為計(jì)算列等。 |
|
INFORMATION_SCHEMA .COLUMNS |
返回當(dāng)前數(shù)據(jù)庫(kù)中當(dāng)前用戶(hù)可以訪問(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)前用戶(hù)具有權(quán)限的所有數(shù)據(jù)庫(kù)及其基本信息。 |
|
INFORMATION_SCHEMA .TABLES |
返回當(dāng)前用戶(hù)具有權(quán)限的當(dāng)前數(shù)據(jù)庫(kù)中的所有表或者視圖及其基本信息。 |
|
INFORMATION_SCHEMA .VIEWS |
返回當(dāng)前數(shù)據(jù)庫(kù)中的當(dāng)前用戶(hù)可以訪問(wèn)的視圖及其所有者、定義等信息。 |
由于這些信息架構(gòu)都是以視圖的方式存在的，因此我們可以很方便地獲得并利用需要的信息。
例如，我們要得到某個(gè)表有多少列，可以使用以下語(yǔ)句：
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='mytable'
?
?
---- 應(yīng)用 ----------------------------------------------------------------------
--1 ：獲取當(dāng)前數(shù)據(jù)庫(kù)中的所有用戶(hù)表
select Name from sysobjects where xtype='u' and status>=0
--2 ：獲取某一個(gè)表的所有字段
select name from syscolumns where id=object_id(N' 表名 ')
--3 ：查看與某一個(gè)表相關(guān)的視圖、存儲(chǔ)過(guò)程、函數(shù)
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like N'% 表名 %'
--4 ：查看當(dāng)前數(shù)據(jù)庫(kù)中所有存儲(chǔ)過(guò)程
select name as 存儲(chǔ)過(guò)程名稱(chēng) from sysobjects where xtype='P'
--5 ：查詢(xún)用戶(hù)創(chuàng)建的所有數(shù)據(jù)庫(kù)
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
--6 ：查詢(xún)某一個(gè)表的字段和數(shù)據(jù)類(lèi)型
select column_name,data_type from information_schema.columns
where table_name = N' 表名 '
--7: 獲取數(shù)據(jù)庫(kù)文件路徑
select ltrim(rtrim(filename)) from 數(shù)據(jù)庫(kù)名 ..sysfiles where charindex('MDF',filename)>0
or
select ltrim(rtrim(filename)) from 數(shù)據(jù)庫(kù)名 ..sysfiles where charindex('LDF',filename)>0
--8: 獲取某一個(gè)表的基本信息
sp_MShelpcolumns N' 表名 '
--9: 獲取某一個(gè)表的主鍵、外鍵信息
exec sp_pkeys N' 表名 '
exec sp_fkeys? N' 表名 '
--10: 判斷某一個(gè)表是否存在某一列 ( 字段 )
if exists(select 1 from syscolumns where id=object_id(N' 表名 ) and name=N' 字段 ')
??? print? N' 存在 '
else
??? print? N' 不存在 '
?
下面給出了一個(gè)存儲(chǔ)過(guò)程，它的作用是自動(dòng)將當(dāng)前數(shù)據(jù)庫(kù)的用戶(hù)存儲(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ò)。
來(lái)源：http://www.cnblogs.com/Spring/archive/2008/05/28/1209092.html?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
