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