How can I search through a SQL Server database schema to find StoredProcs with a specified set of args?

Question

I am trying to deduce which Stored Proc is used to return a specific set of data.

The challenge is that the database has hundreds of Stored Procs. Is there a way I can query the schema to locate all StoredProcs that have parameters named, for instance:

Unit
Member
BegDate
EndDate

...or, barring that, find SPs that take four args?

That would narrow things down a bit and ameliorate matters.

Answer 1

All of the information you want to find about stored procedures, you can find in tables like INFORMATION_SCHEMA.PARAMETERS, SYS.PARAMATERS, SYS.PROCEDURES, SYS.SQL_MODULES, etc.

Your issue can be solved by querying the PARAMETER_NAME in INFORMATION_SCHEMA.PARAMETERS.

e.g.

; WITH T AS (SELECT [specific_name] FROM information_schema.parameters WHERE [parameter_name] = '@Unit'
UNION ALL
SELECT [specific_name] FROM information_schema.parameters WHERE [parameter_name] = '@Member'
UNION ALL
SELECT [specific_name] FROM information_schema.parameters WHERE [parameter_name] = '@BegDate'
UNION ALL
SELECT [specific_name] FROM information_schema.parameters WHERE [parameter_name] = '@EndDate')
SELECT [specific_name] 
FROM T
GROUP BY [specific_name] HAVING COUNT(*) = 4

Or to just find all procedures with 4 parameters:

SELECT [specific_name] FROM information_schema.parameters GROUP BY [specific_name] HAVING COUNT(*) = 4