How to find stored procedures in sql server by Different ways

No.of Views2708
Bookmarked2 times
Downloads 
Votes0
By  RRaveen   On  27 Jul 2011 21:07:14
Tag : Sql Servers , Stored Procedures
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.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

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.

 
Sign Up to vote for this article
 
About Author
 
RRaveen
Occupation-Software Engineer
Company-TGS
Member Type-Gold
Location-Singapore
Joined date-03 Jun 2009
Home Page-codegain.com
Blog Page-www.codegain.com
- B.Sc. degree in Computer Science. - 4+ years experience in Visual C#.net and VB.net - Obsessed in OOP style design and programming. - Designing and developing Network security tools. - Designing and developing a client/server application for sharing files among users in a way other than FTP protocol. - Designing and implementing GSM gateway applications and bulk messaging. - Windows Mobile and Symbian Programming - Having knowledge with ERP solutions
 
 
Other popularSectionarticles
Comments
By:Tanuj KumarDate Of Posted:12/23/2011 2:55:37 AM
Stored Procedure in SQL Server
Check out this link... http://mindstick.com/Articles/79be36ec-d4e6-4ef5-ae5d-c65fd7ede2b9/?Stored%20Procedure%20in%20SQL Thanks
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top