SQL Server 2000 ——系統資料表和系統檢視表

一、系統資料表

資料字典的詳細資料請查SQL SERVER BOL，這裡僅列出一部分。

1.1、sysservers

1、查看所有本機伺服器及連結的伺服器

select * from master..sysservers

1.2、sysdatabases

1：查詢非sa建立的所有資料庫

select * from master..sysdatabases

where sid not in(select sid from master..syslogins where name='sa')

--或者

select dbid, name AS DB_NAME from master..sysdatabases

where sid <> 0x01

1.3、sysobjects

1：擷取當前資料庫中的所有使用者表

select name from sysobjects where xtype='U' and status>0

為什麼要加status>0，因為表dtproperties，雖然該表的xtype為U，實質上它是系統資料表。

dtproperties這個表裡儲存的是關係圖，如果沒建關係圖，就是空的。

注意：這個表只是資料庫物件的屬性，如果想要看錶、索引詳細的屬性，查看sysindexes。

2：查看當前資料庫中所有預存程序

select name as 預存程序名稱 from sysobjects where xtype='P' and status>0

為什麼要加status>0，是為了去掉當前資料庫中的系統預存程序。

註：該系統資料表中type與xtype的區別是什嗎？

Type是在SQL SERVER 6.0就有的，xType在SQL SERVER 7.0才出現，Type的保留只是為了向後相容。每種資料庫物件的類型詳見SQL SERVER BOL。

1.4、syscolumns

1：擷取表或視圖的所有欄位，預存程序或函數的所有參數

select name from syscolumns where id=object_id('表名')

1.5、sysproperties

1：怎麼把SQL SERVER中表設計和表注釋讀出來

--表的注釋全在sysproperties裡

select b.name,value from sysproperties as a,sysobjects as b

where a.id=b.id and b.name='表名'

1.6、sysindexes

1：根據叢集索引，快速查詢表的行數

SELECT rowcnt,indid FROM sysindexes WHERE id=OBJECT_ID('tableName')and indid < 2

注意：使用這種方法可能不精確，因為系統的統計資訊在某些時候不一定是準確的。關於統計的維護(dbcc updateusage())，詳見《SQL SERVER 效能最佳化——查詢最佳化》series。

2：查看索引表資訊

select

table_Name=sysobjects.Name,

    index_Name=sysindexes.Name,

    Type=sysobjects.type,

    分配索引頁=sysindexes.reserved,

    使用索引頁=sysindexes.used,

    葉子層頁=sysindexes.Dpages,

    非葉子層頁=sysindexes.used-sysindexes.Dpages,

    rows=sysindexes.rowcnt

from sysindexes left outer join sysobjects on sysindexes.id=sysobjects.id

where sysindexes.indid>0 and sysindexes.indid<255 and sysindexes.status & 64=0

注意：若發現非葉子層的頁數為負數,最好是運行DBCC UPDATEUSAGE ('dbname','tbname','ixname')來更新一下sysindexes的資訊

1.7、syslogins

SQL SERVER 伺服器的登入資訊，比如：sa，有關登入、使用者、角色的資訊詳見《SQL SERVER 2000 管理——安全——使用者權限》。

select * from syslogins

select * from sysxlogins

sysxlogins是syslogins的精簡版，BOL中沒有說明，不推薦使用。

 

1.8、sysprocesses

1：查看使用者進程資訊

select spid,uid,syslogins.name,login_time,net_address from sysprocesses,syslogins where sysprocesses.sid=syslogins.sid

2：查看資料庫啟動時間

select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1

1.9、sysdepends

1：查看與某一個表相關的視圖、預存程序、函數

select * from sysdepends where depid=object_id('表名')

--或者

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

注意：這種查法，只適用在沒有with Encryption選項，即沒有加密該對象時。

--或者

sp_depends

注意：這個表的統計資訊並不準確，沒有什麼好的辦法，查詢結果只可用於參考。

1.10、sysmessages

SQL SERVER返回的內部錯誤都有在這裡，可自行定義進行錯誤的添加，但一般我習慣於建立一個錯誤的表來定義自己程式中的錯誤。

select * From master..sysmessages where error=5037

1.11、sysfiles、sysfilegroups

1、查詢當前資料庫的檔案使用方式

select name,filename,size/128 as 'used(M)',case maxsize/128 when 0 then 'no limit' else cast(maxsize/128 as varchar(10)) end as'total(M)' from sysfiles

2、查詢當前資料庫的表所在檔案組

select distinct a.id,a.name,b.groupid,c.groupname from sysobjects a inner join sysindexes b on a.id=b.id

inner join sysfilegroups c on b.groupid=c.groupid

where a.xType='U' and a.status>0 order by a.name

sysfiles1是sysfiles的精簡版，BOL中沒有說明，不推薦使用。

二、系統檢視表

在master資料庫中有INFORMATION_SCHEMA和system_function_schema兩個使用者，它們的登入是<無>，這是系統內建的兩個使用者。

INFORMATION_SCHEMA擁有自已的視圖，在SQL Server 2000中沒有被廣泛使用，因為很多時候都可以從系統資料表中得到我們想要的結果，同樣到了SQL Server 2005中，被廣泛使用的仍然是sys所擁有的視圖，SQL Server 2005的相關內容詳見後續《SQL Server 2005》series文章。舉例如下：

1、查詢某個表的哪些欄位不允許為空白

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS

where IS_NULLABLE='NO' and TABLE_NAME='stb_User'

2、查詢某個表的鍵約束

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

where TABLE_NAME='stb_User'

註：鍵約束，是指除了CHECK、NOT NULL外的約束，即PK，FK，UNIQUE，DEFAULT不是約束。