SQL Server – Return a list of parameters from a Stored Procedure

By Dan in Development, SQL Server October 30, 2014 0 Comment

In SQL Server, you can query the database to return the parameters that a Stored Procedure contains. This can be achieved like so:

SELECT
    p.name AS Parameter,
    t.name AS [Type]
FROM <DATABASE NAME>.sys.procedures sp
JOIN <DATABASE NAME>.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 = '<STORED PROCEDURE NAME>' AND t.name != 'sysname'

You can change the SELECT to a COUNT(*) if you just want the number of parameters.

Credit: JodyT – http://stackoverflow.com/a/15431637/1914145