SQL Server 系統資料表儲存中繼資料，分為「主資料庫 (master) 系統表」與「各資料庫系統表」兩大類。注意：直接查詢/修改系統表已不建議，請改用目錄檢視 (Catalog Views, 如 sys.databases, sys.tables, sys.columns) 或資訊結構描述檢視 (INFORMATION_SCHEMA.*)。
| 系統表 | 用途 | 核心欄位範例 |
|---|---|---|
| sysaltfiles | 所有資料庫檔案路徑與大小 | fileid, groupid, size, maxsize, growth, dbid, name, filename |
| syscharsets | 字符集與排序規則定義 | type, id, csid, status, name, definition |
| sysconfigures | 伺服器組態選項 (sp_configure) | comment: 配置值存儲 |
| syscurconfigs | 目前生效組態值 | value, config |
| sysdatabases | 伺服器上所有資料庫 | dbid, name, sid, mode, status, crdate, filename |
| syslanguages | 支援語言清單 | langid, dateformat, datefirst, name, alias |
| syslogins | 伺服器登入帳戶 | sid, name, password, dbname, language, isntname, isntgroup |
| sysoledbusers | 連結伺服器登入對應 | rmtsrvid, loginsid, rmtloginame, rmtpassword |
| sysprocesses | 目前執行緒/連線程序 | spid, kpid, blocked, waittype, lastwaittype, dbid, uid, loginame, hostname, program_name, login_time, cmd, cpu, physical_io, memusage, open_tran |
| sysremotelogins | 遠端登入對應 | remoteserverid, sid, remoteusername |
| 系統表 | 用途 | 核心欄位範例 |
|---|---|---|
| syscolumns | 資料表/視圖欄位定義 | id, name, xtype, length, xprec, xscale, cdefault, status, colid |
| sysconstraints | 條件約束定義 | id, colid, status, constid |
| sysfilegroups | 檔案群組 | groupid, groupname, status |
| sysfiles | 資料庫檔案 | fileid, groupid, size, maxsize, growth, name, filename |
| sysforeignkeys | 外來鍵關聯 | constid, fkeyid, rkeyid, fkey, rkey |
| sysindexes | 索引與統計資料 | id, indid, name, first, root, dpages, used, rowcnt, status |
| sysmembers | 資料庫角色成員 | memberuid, groupuid |
| sysobjects | 所有資料庫物件 | id, name, xtype (U=table, P=proc, V=view, TR=trigger), uid, crdate, refdate |
| syspermissions | 物件權限 | id, grantee, grantor, type, action, protecttype |
| systypes | 使用者定義資料類型 | xtype, name, length, xprec, xscale, default, rule, domain |
| sysusers | 資料庫使用者 | uid, name, gid, loginname, createdate |
-- 取代 sysdatabases
SELECT * FROM sys.databases;
-- 取代 sysobjects + sysusers
SELECT o.name, o.type_desc, u.name AS owner
FROM sys.objects o
JOIN sys.database_principals u ON o.principal_id = u.principal_id;
-- 取代 syscolumns
SELECT c.name, t.name AS type_name, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.YourTable');
-- 取代 sysindexes
SELECT i.name, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.YourTable');
目錄檢視提供穩定介面、版本相容、效能優化，請優先使用。
