How do I get the list of all stored procedures and their parameters starting with a certain prefix?

sql-server stored-procedures sql-server-2000
the_drow picture the_drow · Feb 28, 2010 · Viewed 20.5k times · Source

Is there a way to query the database and retrieve a list of all stored procedures and their parameters?
I am using SQL Server 2000.

Answer

AdaTheDev picture AdaTheDev · Feb 28, 2010

To get information on the stored procedures:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

To find the sprocs starting with a certain prefix (e.g. "usp"):

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'usp%'

To find all the parameters for a stored procedure:

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='YourSprocName'

To find all the parameters for all stored procedures starting with a certain prefix:

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME LIKE 'usp%'

Related questions

SQL Server 2000: How to exit a stored procedure?

How can I exit in the middle of a stored procedure? I have a stored procedure where I want to bail out early (while trying to debug it). I've tried calling RETURN and RAISERROR, and the sp keeps on running: …

Read More
Check if record exists, if yes "update" if not "insert"

I want to check table PREMIUM_SERVICE_USER if any records exists for strClientID update timeValid for +30 if no records for strClientID insert to premium_service_user table. What am I doing wrong? It increases timeValid for +30 days but inserts …

Read More
SQL Error: Incorrect syntax near the keyword 'End'

Need help with this SQL Server 2000 procedure. The problem is made difficult because I'm testing procedure via Oracle SQL Developer. I'm running the procedure to iterate column with new sequence of numbers in Varchar format for those who have null …

Read More