0

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.

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862

1 Answers1

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
ZLK
  • 2,864
  • 1
  • 10
  • 7
  • Is the beginning semicolon really supposed to be there? What is its raison d'etre? – B. Clay Shannon-B. Crow Raven Dec 11 '15 at 15:57
  • This worked like a champ, although I don't know why (I don't understand the usage of ";" at the beginning or "WITH T". Anyway, I will henceforth think of you as the SQL Server Metadata Whisperer, Zebra Lion Kangaroo. – B. Clay Shannon-B. Crow Raven Dec 11 '15 at 16:04
  • 1
    @B.ClayShannon The semicolon just terminates the previous statement (if there was one). If there wasn't a previous statement, it does nothing (it's harmless). CTEs require the previous statement to be terminated (if there was one). – ZLK Dec 12 '15 at 06:31