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.

Clemons

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

Granulation

Should I change the WHERE SPECIFIC_NAME = ... to LIKE "MyTable_*"? – Clemons

Updated my answer with 2 further examples, to search by a certain prefix. – Granulation

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'

Lona

Answer 3

To show a list of all procedures and their parameters, it would be in this way:

SELECT o.name AS [Procedure name], p.name as [Parameter name] 
FROM sys.parameters p INNER JOIN sysobjects o ON p.object_id = o.id
WHERE o.name LIKE 'prefix%' AND o.xtype = 'P'

It works in SQL Server 2016 but I guess it works in older versions too.

Cunctation

Answer 4

the following query returns the procedures, functions and filters by a prefix. I am not sure though, if it would work on sql server 2000. I leave it here the reference anyway, because it is a good useful query.

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema], 
SO.name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
COALESCE(P.parameter_id,0) AS [ParameterID],
COALESCE(P.name, 'NO PARAMETER')  AS [ParameterName],
COALESCE(TYPE_NAME(P.user_type_id),'')  AS [ParameterDataType],
COALESCE(P.max_length,0) AS [ParameterMaxBytes],
COALESCE(P.is_output,0) AS [IsOutPutParameter]
FROM sys.objects AS SO
LEFT OUTER JOIN sys.parameters AS P 
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID 
FROM sys.objects
WHERE TYPE IN ('P','FN'))
AND SO.NAME LIKE 'U%'   --starting with a certain prefix 
ORDER BY [Schema], SO.name, P.parameter_id
GO

Foilsman