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

Question

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 1

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

Answer 2

try this one :

select o.name,p.name from sys.all_parameters p inner join sys.all_objects o on p.object_id = o.object_id 
where o.type = 'P'