SQL SERVER 获取对象(存储过程、函数等)参数信息

 

CREATE PROCEDURE [Common].[ProcLoadFunctionParameters]

    @inObjectName sysname

AS

    BEGIN

        SELECT  sp.object_id AS FunctionId ,

                sp.name AS FunctionName ,

(CASE WHEN param.is_output = 1 THEN 'OUTPUT' ELSE 'IN' END) AS ParamType,

                ISNULL(param.name, '') AS ParamName ,

                ISNULL(usrt.name, '') AS [DataType] ,

                ISNULL(baset.name, '') AS [SystemType] ,

                CAST(CASE WHEN baset.name IS NULL THEN 0

                          WHEN baset.name IN ( 'nchar', 'nvarchar' )

                               AND param.max_length <> -1

                          THEN param.max_length / 2

                          ELSE param.max_length

                     END AS INT) AS [Length] ,

                ISNULL(parameter_id, 0) AS SortId,

'' AS ParamReamrk 

        FROM    sys.objects AS sp

                INNER JOIN sys.schemas b ON sp.schema_id = b.schema_id

                LEFT OUTER JOIN sys.all_parameters AS param ON param.object_id = sp.object_id

                LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id

                LEFT OUTER JOIN sys.types AS baset ON ( baset.user_type_id = param.system_type_id

                                                        AND baset.user_type_id = baset.system_type_id

                                                      )

                                                      OR ( ( baset.system_type_id = param.system_type_id )

                                                           AND ( baset.user_type_id = param.user_type_id )

                                                           AND ( baset.is_user_defined = 0 )

                                                           AND ( baset.is_assembly_type = 1 )

                                                         )

                LEFT OUTER JOIN sys.extended_properties E ON sp.object_id = E.major_id

        WHERE   sp.object_id = OBJECT_ID(@inObjectName)

                AND sp.type IN ( 'FN', 'IF', 'TF', 'P' )

                AND ISNULL(sp.is_ms_shipped, 0) = 0

                AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'

        ORDER BY sp.name ,

                param.parameter_id ASC;

    END;

 

GO