SQL Server 實用系統預存程序與中繼資料查詢技巧

掌握系統預存程序與目錄檢視可大幅提升資料庫探索與自動化開發效率。本文整理常用的中繼資料查詢技巧，涵蓋資料庫、資料表、欄位、預存程序與 ADO.NET 參數發現。

快速取得伺服器/資料庫清單

-- 所有資料庫名稱、大小、備註
EXEC sp_databases;
-- 或查詢目錄檢視 (SQL 2005+ 推薦)
SELECT name, database_id, create_date, state_desc FROM sys.databases;

列出資料庫內所有使用者資料表

USE Northwind; GO
EXEC sp_tables @table_type = "'TABLE'";  -- 只列 TABLE，排除 SYSTEM TABLE/VIEW
-- 現代寫法
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

取得資料表欄位完整定義

EXEC sp_columns 'Orders';  -- 傳回欄位名、型別、長度、精度、Identity、Nullable 等
-- 現代寫法 (資訊更豐富)
SELECT 
    COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, 
    NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, 
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IsIdentity
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Orders';

列出所有使用者預存程序

-- 老寫法
SELECT * FROM sysobjects WHERE type = 'P' AND category = 0;  -- category=0 排除系統程序
-- 推薦：INFORMATION_SCHEMA
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE';
-- 或 sys.procedures (SQL 2005+)
SELECT name, create_date, modify_date FROM sys.procedures;

ADO.NET 動態發現預存程序參數

利用 SqlCommandBuilder.DeriveParameters 自動填入參數集合，適用於通用執行器/程式碼產生器：

using (SqlConnection conn = new SqlConnection(connStr)) {
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("SalesByCategory", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlCommandBuilder.DeriveParameters(cmd);  // 需參考 System.Data.dll
        
        foreach (SqlParameter p in cmd.Parameters) {
            Console.WriteLine($"Name:{p.ParameterName}, Size:{p.Size}, Type:{p.DbType}, " +
                $"Direction:{p.Direction}, IsNullable:{p.IsNullable}");
        }
    }
}

其他實用系統函數/預存程序速查

最佳實務建議

• 優先使用 INFORMATION_SCHEMA / sys.* 目錄檢視：ANSI 標準、跨版本相容、效能優於舊版系統表。
• 避免直接查詢 sysobjects/syscolumns 等舊版系統表：結構可能隨版本變更，且需特別權限。
• 善用 OBJECT_ID(), COLUMNPROPERTY(), INDEXPROPERTY() 等中繼資料函數：撰寫動態 SQL 時極為方便。