How to find Stored Procedures in your Database

In case you are in a situation where you are looking for a stored procedure in SQL but don't remember the name or forgot the parameters used. You are on the right blog to get you those data in seconds :)

Execute below query in your Database to get a list of all Stored Procedures

select * from sysobjects where id in
(select id from syscomments where text like '%exec%')
order by [name]

Execute below query to get the parameters for a particular stored procedure

select t1.[name] as [SP_name],t2.[name] as [Parameter_name],
t3.[name] as [Type],t2.[Length],t2.colorder as [Param_order]
from sysobjects t1
inner join syscolumns t2 on t1.[id]=t2.[id]
inner join systypes t3 on t2.xtype=t3.xtype
where t1.[name]='name of the stored procedure'
order by [Param_order]

Execute below query to get more detailed info on stored procedures and their parameters.

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],
SO.name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
P.parameter_id AS [ParameterID],
P.name AS [ParameterName],
TYPE_NAME(P.user_type_id) AS [ParameterDataType],
P.max_length AS [ParameterMaxBytes],
P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER 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'))
ORDER BY [Schema], SO.name, P.parameter_id

Happy learning!