SQL SERVER gets the object (stored procedure, function, and so on) parameter information

CREATE PROCEDURE [Common]. [Procloadfunctionparameters]

@inObjectName sysname

As

BEGIN

SELECT sp.object_id as FunctionId,

Sp.name as FunctionName,

(case is 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 if Baset.name is NULL then 0

When baset.name in (' nchar ', ' nvarchar ')

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 joins Sys.all_parameters as param on param.object_id = sp.object_id

Left OUTER joins Sys.types as usrt on usrt.user_type_id = param.user_type_id

Left OUTER joins 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 joins 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

SQL SERVER gets the object (stored procedure, function, and so on) parameter information