依預存程序名稱取得程式碼內容：通用查詢腳本 (SQL Server 2000/2005/2008+)

利用系統視圖 sys.sql_modules (2005+) 或 syscomments (2000) 可動態取得預存程序/函數/檢視/觸發器的完整定義文字。

現代通用腳本 (SQL Server 2005+ 推薦)

CREATE PROCEDURE dbo.GetObjectDefinition
    @ObjectName NVARCHAR(500) -- 格式: Schema.Name 或 Name
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @ObjectId INT = OBJECT_ID(@ObjectName);
    IF @ObjectId IS NULL
    BEGIN
        RAISERROR('物件不存在: %s', 16, 1, @ObjectName);
        RETURN;
    END

    SELECT 
        OBJECT_SCHEMA_NAME(@ObjectId) + '.' + OBJECT_NAME(@ObjectId) AS ObjectName,
        type_desc AS ObjectType,
        definition AS DefinitionText
    FROM sys.sql_modules
    WHERE object_id = @ObjectId;
END;

使用範例

EXEC dbo.GetObjectDefinition 'dbo.usp_GetCustomerOrders';
EXEC dbo.GetObjectDefinition 'Sales.GetRevenueByRegion';

相容 SQL Server 2000 寫法

CREATE PROCEDURE dbo.GetObjectDefinition_2000
    @ProcedureName NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON;
    
    IF EXISTS (SELECT * FROM dbo.syscomments WHERE id = OBJECT_ID(@ProcedureName))
    BEGIN
        SELECT 
            c.text,
            c.encrypted,
            c.number,
            xtype = CONVERT(NCHAR(2), o.xtype),
            DATALENGTH(c.text),
            CONVERT(VARBINARY(8000), c.text),
            0
        FROM dbo.syscomments c
        JOIN dbo.sysobjects o ON o.id = c.id
        WHERE c.id = OBJECT_ID(@ProcedureName)
        ORDER BY c.number, c.colid
        OPTION (ROBUST PLAN);
    END
    ELSE
    BEGIN
        PRINT '物件不存在: ' + @ProcedureName;
    END
END;

進階：批次匯出所有可程式化物件

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS ObjectName,
    o.type_desc AS ObjectType,
    m.definition AS DefinitionText
FROM sys.objects o
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE o.type IN ('P', 'FN', 'IF', 'TF', 'V', 'TR') -- Proc, Scalar/Inline/Table Function, View, Trigger
ORDER BY SchemaName, ObjectName;

關鍵系統視圖對照

注意事項

• 加密物件 (WITH ENCRYPTION) 定義不可讀，definition 為 NULL
• 臨時表 (#temp) 不在 sys.sql_modules 中，需在建立的工作階段查詢
• 動態 SQL (EXEC(@sql)) 不會記錄在此，需用 Profiler/Extended Events 捕捉

掌握 sys.sql_modules 讓程式碼檢索、文件化、版本比對、自動化部署驗證成為可能。