SQL Server 2000 系統表應用技巧：查詢表結構、相依物件與預存程序

核心系統表

實用查詢技巧

1. 取得所有使用者表

SELECT name 
FROM sysobjects 
WHERE xtype = 'U' AND status >= 0;

xtype='U' = 使用者表，status>=0 排除系統內部表。

2. 取得指定表的所有欄位

SELECT name 
FROM syscolumns 
WHERE id = OBJECT_ID('表名');

3. 查詢與某表相關的視圖、預存程序、函數

SELECT a.* 
FROM sysobjects a, syscomments b 
WHERE a.id = b.id 
  AND b.text LIKE '%表名%';

透過 syscomments.text 全文搜尋物件定義中引用的表名。

4. 列出所有預存程序

SELECT name AS 預存程序名稱 
FROM sysobjects 
WHERE xtype = 'P';

xtype='P' = 預存程序。

擴充應用：完整表結構查詢

SELECT 
    CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
    a.colorder AS 欄位序號,
    a.name AS 欄位名,
    CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 識別欄,
    CASE WHEN EXISTS (
        SELECT 1 FROM sysindexes si
        JOIN sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
        JOIN syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
        JOIN sysobjects so ON so.name = si.name AND so.xtype = 'PK'
        WHERE sc.id = a.id AND sc.colid = a.colid
    ) THEN '√' ELSE '' END AS 主鍵,
    b.name AS 資料類型,
    a.length AS 長度,
    COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,
    ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小數位數,
    CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空值,
    ISNULL(e.text, '') AS 預設值,
    ISNULL(g.[value], '') AS 欄位說明
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid AND g.name = 'MS_Description'
ORDER BY a.id, a.colorder;

常用 xtype 代碼對照

注意事項

• SQL Server 2005+ 建議使用 INFORMATION_SCHEMA 檢視或 sys.* 目錄檢視（如 sys.tables, sys.columns）
• 直接查詢系統表雖快但非官方支援介面，升級可能失效
• syscomments 在大型預存程序中可能被截斷，建議用 OBJECT_DEFINITION()

總結

熟練掌握 sysobjects、syscolumns、syscomments 三大核心系統表，配合 OBJECT_ID()、COLUMNPROPERTY() 等中繼資料函數，能快速完成結構查詢、相依性分析、程式碼搜尋等 DBA 與開發常見任務。