SQL Server T-SQL Query for Stored Procedures with Parameters

List of SPs and Parameters

I often want to get a list of all the stored procedures and their parameters for documentation purposes. The following queries work well for me. Most of our SPs have a prefix, so the queries include the ability to filter the list using a prefix, postfix, or keyword.

Using SYS Tables

I originally went down the path of using SYS tables (MSDN):

SELECT

    sp.name AS Name,

    p.name AS Parameter,

    t.name AS [Type]

FROM sys.procedures sp

JOIN sys.parameters p

    ON sp.object_id = p.object_id

JOIN sys.types t

    ON p.system_type_id = t.system_type_id

WHERE sp.name LIKE 'prefix%' -- '%postfix' or '%keyword%'

ORDER BY

       sp.name,

       p.name

;

Using INFORMATION_SCHEMA

I didn't really like having to use JOINs and looked for a simpler method. This is when I ran accross the INFORMATION_SCHEMA tables (MSDN). This resulted in a much cleaner query:

SELECT

       SPECIFIC_NAME,

       ORDINAL_POSITION,

       PARAMETER_MODE,

       PARAMETER_NAME,

       DATA_TYPE

FROM

       INFORMATION_SCHEMA.PARAMETERS

WHERE

       SPECIFIC_NAME LIKE 'prefix%' -- '%postfix' or '%keyword%'

ORDER BY

       SPECIFIC_NAME,

       ORDINAL_POSITION

;

Hopefully this helps anyone who is looking for a quick way to generate documentation on Stored Procedures with Parameters.