--    schema_name:  The schema_name provides the name of the schema of the table, it is often a dbo.
--    procedure_name: Provides the name of the procedure/function/trigger referred to in the row.  
--    procedure_type:  This provides a description of the given procedure object.
--    procedure_definition: This provides the text definition of the procedure/function/trigger.  
--          Note: The content will only be populated with meaningful content for SQL objects (non-CLR) 
--          that are unencrypted. Only objects with a definition (not null) are
--          included in this query.  You will need to use a like statement to 
--          meaningfully filter this. 
--    parameter_data:  This provides an xml representation of the procedure parameters. 
--         id: indicates the parameter id for a given procedure.
--         name: provides the name of the parameter name.
--         type: provides the data type of the parameter
--         length: provides the capacity of the parameter
--         nullable: indicates if the column is nullable
--         precision: provides the precision of number types
--    parameter_count:  This provides the total number of parameters for the given procedure object.




With Procedure_CTE As (
    select 
        procedure_id = object_id , 
        schema_name = schema_name( schema_id), 
        procedure_name = name , 
        procedure_type = Replace(sys.objects.type_desc, '_', ' ') , 
        procedure_definition = OBJECT_DEFINITION (object_id) 
    from sys.objects
),
ParameterGroup_CTE as (
    select object_id procedure_id, count(parameter_id) parameter_count
    from    sys.parameters
    group by object_id
),
Query_CTE as (    
select 
        schema_name, 
        procedure_name, 
        procedure_type, 
        procedure_definition 
= (Select procedure_definition = LTrim( procedure_definition) 
For Xml Path(''), Type).query('*/text()'), 
        parameter_data =
         (
            SELECT 
            (
                SELECT 
                   '@id' = parameter_id,
                   '@name' = name,
                   '@type' = Type_Name( system_type_id ),
                   '@length' = max_length ,
                   '@nullable' = CASE WHEN is_nullable = 0 then null ELSE 'true' END,
                   '@precision' = CASE WHEN PRECISION = 0 then null else PRECISION END
                  FROM sys.parameters parameters
                  WHERE Procedure_CTE.procedure_id = object_id
                  order by parameter_id
                  For Xml Path('parameter'), type
            )
            where 0 < IsNull(parameter_count, 0) 
            For Xml Path('procedure'), type
        ),
        parameter_count = IsNull(parameter_count, 0) 
    from Procedure_CTE 
    left join ParameterGroup_CTE
        on ParameterGroup_CTE.procedure_id = Procedure_CTE.procedure_id
    where not procedure_definition is null
)
select *
from Query_CTE
order by procedure_name, schema_name