How to find stored procedures in sql server by Different ways

Introduction

In this codesnippet, i will show, how to find the procedures in sql server using three different ways. such as by name, by content and by parameter names.

1. By Name

-- =============================================    
-- Author:  RRaveen    
-- Create date: 2011-07-28     
-- Description: search stored procedures by name
-- =============================================   
CREATE proc spFindSPByName
   @procname varchar(200)
AS
   SET @procname = '%' + @procname + '%'
   SELECT DISTINCT SO.NAME
   FROM SYSOBJECTS SO (NOLOCK)
   WHERE SO.TYPE = 'P'
   AND SO.NAME LIKE @procname
   ORDER BY SO.Name
GO

This is list procedures match with your name.

2. By Content

-- =============================================    
-- Author:  RRaveen    
-- Create date: 2011-07-28     
-- Description: search procedures by proc content
-- =============================================   
CREATE proc spFindSPByProcedureContent
@proccontent varchar(200)
AS
   SET @proccontent = '%' +@proccontent + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @proccontent
   ORDER BY SO.Name
GO

This is list procedures match with your content.

3. By Parameter Name

-- =============================================    
-- Author:  RRaveen    
-- Create date: 2011-07-28     
-- Description: search stored procedures by parameters names   
-- =============================================   
create proc spFindSPByParams
@procparam nvarchar(200)
As
	BEGIN	
		declare @sql nvarchar(MAX) 
		declare @params  nvarchar(MAX) 
		set @params='@procparam nvarchar(200)'
		set @sql ='select * from INFORMATION_SCHEMA.PARAMETERS	where PARAMETER_NAME lIKE +@procparam +''%'' ORDER BY SPECIFIC_NAME'	
		exec sp_executesql @sql,@params,@procparam	
	END

This is list procedures match with your parameter name.

That's all hope help and thank you.