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%'
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.
