sql server查詢可編程對象定義的方式對比以及整合

註意：

IF OBJECT_ID(N'[dbo].[usp_helptext2]', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[usp_helptext2];
END
GO
 
--==================================
-- 功能: 查看可編程對象定義
-- 說明: 支持用戶定義類型，可以運行於SQL Server 2005+
-- 創建: yyyy-MM-dd hh:mm-hh:mm XXX 創建內容描述
-- 修改: yyyy-MM-dd hh:mm-hh:mm XXX 修改內容描述
--==================================
CREATE PROCEDURE [dbo].[usp_helptext2]
(
     @nvcObjectName AS NVARCHAR(776)                    -- 對象名稱，可以支持的對象類型為(C、D、FN、IF、P、R、RF、TF、TR、U、V)
    ,@nvcComputedColumnName AS NVARCHAR(128) = NULL        -- 計算列名稱（如果@nvcObjectName的對象類型為U，則該參數表示計算列名稱)
)
AS
BEGIN
    SET NOCOUNT ON;
 
    SET @nvcObjectName = ISNULL(@nvcObjectName, N'');
    IF (@nvcObjectName = N'')
    BEGIN
        RAISERROR(16902, -1, -1,N'usp_helptext2', N'@nvcObjectName');
        RETURN(1);
    END
 
    SET @nvcComputedColumnName = ISNULL(@nvcComputedColumnName, N'');
 
    DECLARE @tntRetVal AS TINYINT;
    SET @tntRetVal = 0;
 
    DECLARE @tblObjDef AS TABLE (
        [Text] NVARCHAR(1000) NULL
    );
 
    DECLARE
         @intObjectID AS INT
        ,@chaType AS CHAR(2)
        ,@nvcText AS NVARCHAR(MAX);
    SELECT
         @intObjectID = 0
        ,@chaType = ''
        ,@nvcText = N'';
    SELECT
         @intObjectID = [object_id]
        ,@chaType = [type]        
    FROM [sys].[all_objects]
    WHERE 
        [type] IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'U', 'V')
        AND [name] = PARSENAME(@nvcObjectName, 1);
 
    IF (@nvcComputedColumnName > N'')    -- 獲取計算列定義
    BEGIN
        IF (@chaType NOT IN ('S', 'U', 'TF'))
        BEGIN
            RAISERROR(15218, -1, -1, @nvcObjectName);
            RETURN(1);
        END
 
        INSERT INTO @tblObjDef ([Text])        
        EXEC [sys].[sp_helptext]
             @objname = @nvcObjectName                -- nvarchar(776)
            ,@columnname = @nvcComputedColumnName    -- sysname
 
        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END
 
        SELECT 
            @nvcText = ISNULL([Text], N'')
        FROM @tblObjDef;            
    END
    ELSE IF (@intObjectID <> 0)    -- 獲取除計算列和伺服器觸發器以外的所有對象類型的定義
    BEGIN
        SET @nvcText = OBJECT_DEFINITION(@intObjectID);
 
        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END
    END
    ELSE IF (@intObjectID = 0)    -- 嘗試獲取伺服器觸發器定義
    BEGIN
        SELECT 
            @nvcText = T1.[definition]
        FROM [sys].[server_sql_modules] AS T1
            INNER JOIN [sys].[server_triggers] AS T2
                ON [T1].[object_id] = [T2].[object_id]
        WHERE T2.[name] = @nvcObjectName;
 
        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END
    END
 
    SELECT 
        @nvcText AS [Text];
 
    RETURN(@tntRetVal);
END
GO
 

• https://msdn.microsoft.com/en-us/library/ms176112.aspx
• https://msdn.microsoft.com/en-us/library/ms175081.aspx
• https://msdn.microsoft.com/en-us/library/ms188034.aspx
• https://msdn.microsoft.com/en-us/library/ms184389.aspx
• https://msdn.microsoft.com/en-us/library/ms176090.aspx
• https://msdn.microsoft.com/en-us/library/ms188746.aspx
• https://msdn.microsoft.com/en-us/library/ms176054.aspx
• https://msdn.microsoft.com/en-us/library/ms187794.aspx

•