sp_procedure_params_rowset (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_procedure_params_rowset(nvarchar @procedure_name
, int @group_number
, nvarchar @procedure_schema
, nvarchar @parameter_name)

MetaData:

   
create procedure sys.sp_procedure_params_rowset  
(  
    @procedure_name     sysname,  
    @group_number       int = 1,  
    @procedure_schema   sysname = null,  
    @parameter_name     sysname = null  
)  
as  
    select  
        PROCEDURE_CATALOG           = s_pp.PROCEDURE_CATALOG,  
        PROCEDURE_SCHEMA            = s_pp.PROCEDURE_SCHEMA,  
        PROCEDURE_NAME              = convert(nvarchar(134),  
                                                s_pp.PROCEDURE_NAME +';'+  
                                                ltrim(str(coalesce(s_pp.procedure_number,@group_number,1), 5))),  
        PARAMETER_NAME              = s_pp.PARAMETER_NAME,  
        ORDINAL_POSITION            = s_pp.ORDINAL_POSITION,  
        PARAMETER_TYPE              = s_pp.PARAMETER_TYPE,  
        PARAMETER_HASDEFAULT        = s_pp.PARAMETER_HASDEFAULT,  
        PARAMETER_DEFAULT           = s_pp.PARAMETER_DEFAULT,  
        IS_NULLABLE                 = s_pp.IS_NULLABLE,  
        DATA_TYPE                   = s_pp.DATA_TYPE_28, --  for backward compatibility  
        CHARACTER_MAXIMUM_LENGTH    = s_pp.CHARACTER_MAXIMUM_LENGTH_28, --  for backward compatibility  
        CHARACTER_OCTET_LENGTH      = s_pp.CHARACTER_OCTET_LENGTH_28, --  for backward compatibility  
        NUMERIC_PRECISION           = s_pp.NUMERIC_PRECISION,  
        NUMERIC_SCALE               = s_pp.NUMERIC_SCALE,  
        DESCRIPTION                 = s_pp.DESCRIPTION,  
        TYPE_NAME                   = s_pp.TYPE_NAME_28, --  for backward compatibility  
        LOCAL_TYPE_NAME             = s_pp.LOCAL_TYPE_NAME_28 --  for backward compatibility  
  
    from  
        sys.spt_procedure_params_view s_pp  
  
    where  
        (@procedure_schema is null and s_pp.PROCEDURE_NAME = @procedure_name)  
        and  
        (  
            @group_number is null or  
            (s_pp.procedure_number = @group_number and s_pp.type in ('P', 'PC')) or  
            (s_pp.procedure_number = 0             and s_pp.type in ('FN', 'TF', 'IF'))  
        ) and  
        (@parameter_name is null or @parameter_name = s_pp.PARAMETER_NAME)  
  
    UNION ALL  
  
    select  
        PROCEDURE_CATALOG           = s_pp.PROCEDURE_CATALOG,  
        PROCEDURE_SCHEMA            = s_pp.PROCEDURE_SCHEMA,  
        PROCEDURE_NAME              = convert(nvarchar(134),  
                                                s_pp.PROCEDURE_NAME +';'+  
                                                ltrim(str(coalesce(s_pp.procedure_number,@group_number,1), 5))),  
        PARAMETER_NAME              = s_pp.PARAMETER_NAME,  
        ORDINAL_POSITION            = s_pp.ORDINAL_POSITION,  
        PARAMETER_TYPE              = s_pp.PARAMETER_TYPE,  
        PARAMETER_HASDEFAULT        = s_pp.PARAMETER_HASDEFAULT,  
        PARAMETER_DEFAULT           = s_pp.PARAMETER_DEFAULT,  
        IS_NULLABLE                 = s_pp.IS_NULLABLE,  
        DATA_TYPE                   = s_pp.DATA_TYPE_28, --  for backward compatibility  
        CHARACTER_MAXIMUM_LENGTH    = s_pp.CHARACTER_MAXIMUM_LENGTH_28, --  for backward compatibility  
        CHARACTER_OCTET_LENGTH      = s_pp.CHARACTER_OCTET_LENGTH_28, --  for backward compatibility  
        NUMERIC_PRECISION           = s_pp.NUMERIC_PRECISION,  
        NUMERIC_SCALE               = s_pp.NUMERIC_SCALE,  
        DESCRIPTION                 = s_pp.DESCRIPTION,  
        TYPE_NAME                   = s_pp.TYPE_NAME_28, --  for backward compatibility  
        LOCAL_TYPE_NAME             = s_pp.LOCAL_TYPE_NAME_28 --  for backward compatibility  
  
    from  
        sys.spt_procedure_params_view s_pp  
  
    where  
        (@procedure_schema is not null and s_pp.object_id = object_id(quotename(@procedure_schema) + '.' + quotename(@procedure_name)))  
        and  
        (  
            @group_number is null or  
            (s_pp.procedure_number = @group_number and s_pp.type in ('P', 'PC')) or  
            (s_pp.procedure_number = 0             and s_pp.type in ('FN', 'TF', 'IF'))  
        ) and  
        (@parameter_name is null or @parameter_name = s_pp.PARAMETER_NAME)  
  
    UNION ALL  
  
    select  
        PROCEDURE_CATALOG           = s_pprv.PROCEDURE_CATALOG,  
        PROCEDURE_SCHEMA            = s_pprv.PROCEDURE_SCHEMA,  
        PROCEDURE_NAME              = convert(nvarchar(134),  
                                                s_pprv.PROCEDURE_NAME +';'+  
                                                ltrim(str(coalesce(s_pprv.procedure_number,@group_number,1), 5))),  
        PARAMETER_NAME              = s_pprv.PARAMETER_NAME,  
        ORDINAL_POSITION            = s_pprv.ORDINAL_POSITION,  
        PARAMETER_TYPE              = s_pprv.PARAMETER_TYPE,  
        PARAMETER_HASDEFAULT        = s_pprv.PARAMETER_HASDEFAULT,  
        PARAMETER_DEFAULT           = s_pprv.PARAMETER_DEFAULT,  
        IS_NULLABLE                 = s_pprv.IS_NULLABLE,  
        DATA_TYPE                   = s_pprv.DATA_TYPE, --  Return value is either int or empty.  
        CHARACTER_MAXIMUM_LENGTH    = s_pprv.CHARACTER_MAXIMUM_LENGTH,  
        CHARACTER_OCTET_LENGTH      = s_pprv.CHARACTER_OCTET_LENGTH,  
        NUMERIC_PRECISION           = s_pprv.NUMERIC_PRECISION,  
        NUMERIC_SCALE               = s_pprv.NUMERIC_SCALE,  
        DESCRIPTION                 = s_pprv.DESCRIPTION,  
        TYPE_NAME                   = s_pprv.TYPE_NAME,  
        LOCAL_TYPE_NAME             = s_pprv.LOCAL_TYPE_NAME  
  
    from  
        sys.spt_procedure_params_return_values_view s_pprv  
  
    where  
        (@procedure_schema is null and s_pprv.PROCEDURE_NAME = @procedure_name)  
        and  
        (  
            @parameter_name is null or  
            (@parameter_name = '@RETURN_VALUE' and s_pprv.type in ('P', 'PC')) or  
            (@parameter_name = '@TABLE_RETURN_VALUE' and s_pprv.type <> 'P' and s_pprv.type <> 'PC')  
        )  
        and  
        (  
            @group_number is null or  
            (s_pprv.procedure_number = 0             and s_pprv.type in ('FN', 'TF', 'IF')) or  
            (s_pprv.procedure_number = @group_number and s_pprv.type in ('P', 'PC'))  
        )  
  
    UNION ALL  
  
    select  
        PROCEDURE_CATALOG           = s_pprv.PROCEDURE_CATALOG,  
        PROCEDURE_SCHEMA            = s_pprv.PROCEDURE_SCHEMA,  
        PROCEDURE_NAME              = convert(nvarchar(134),  
                                                s_pprv.PROCEDURE_NAME +';'+  
                                                ltrim(str(coalesce(s_pprv.procedure_number,@group_number,1), 5))),  
        PARAMETER_NAME              = s_pprv.PARAMETER_NAME,  
        ORDINAL_POSITION            = s_pprv.ORDINAL_POSITION,  
        PARAMETER_TYPE              = s_pprv.PARAMETER_TYPE,  
        PARAMETER_HASDEFAULT        = s_pprv.PARAMETER_HASDEFAULT,  
        PARAMETER_DEFAULT           = s_pprv.PARAMETER_DEFAULT,  
        IS_NULLABLE                 = s_pprv.IS_NULLABLE,  
        DATA_TYPE                   = s_pprv.DATA_TYPE, --  Return value is either int or empty.  
        CHARACTER_MAXIMUM_LENGTH    = s_pprv.CHARACTER_MAXIMUM_LENGTH,  
        CHARACTER_OCTET_LENGTH      = s_pprv.CHARACTER_OCTET_LENGTH,  
        NUMERIC_PRECISION           = s_pprv.NUMERIC_PRECISION,  
        NUMERIC_SCALE               = s_pprv.NUMERIC_SCALE,  
        DESCRIPTION                 = s_pprv.DESCRIPTION,  
        TYPE_NAME                   = s_pprv.TYPE_NAME,  
        LOCAL_TYPE_NAME             = s_pprv.LOCAL_TYPE_NAME  
  
    from  
        sys.spt_procedure_params_return_values_view s_pprv  
  
    where  
        (@procedure_schema is not null and s_pprv.object_id = object_id(quotename(@procedure_schema) + '.' + quotename(@procedure_name)))  
        and  
        (  
            @parameter_name is null or  
            (@parameter_name = '@RETURN_VALUE' and s_pprv.type in ('P', 'PC')) or  
            (@parameter_name = '@TABLE_RETURN_VALUE' and s_pprv.type <> 'P' and s_pprv.type <> 'PC')  
        )  
        and  
        (  
            @group_number is null or  
            (s_pprv.procedure_number = 0             and s_pprv.type in ('FN', 'TF', 'IF')) or  
            (s_pprv.procedure_number = @group_number and s_pprv.type in ('P', 'PC'))  
        )  
  
    order by 2, 3, 5  
    option (OPTIMIZE CORRELATED UNION ALL)