SQL Server: Search And Find Stored Procedure

Several times in the past, I’ve come across situations that needed to find stored procedure by part of its name. I could not remember the exact name of the stored procedure. Instead I knew only part of its name. The databases had thousands of stored procedures. In such situations, searching and finding a stored procedure is difficult. But, fortunately we have several methods to find the stored procedures in SQL Server by its name, part of the name, having a text or having the tables or column used in it. Now, we’ll see the methods one by one.

I. Find Stored Procedure By Its Name Or Partial Name

Find Using Select Query Against:

To find a stored procedure using it full name or partial name, we can use simple select queries against the system tables like sys.procedures, Information_Schema.Routines or syscomments. Below are the simple select queries. In all the below queries, change the Employee text to your search text

1. Sys.Procedures

   sys.procedures system table has the name of the procedure, object id and other details like created date, modified date, etc… So, if you know part of the stored procedure’s name, then you can use this option to find the list of stored procedures having the phrase in the name.

   SELECT 
   	name
   FROM 
      sys.procedures 
   WHERE 
      name LIKE '%Employee%'
   

   

2. INFORMATION_SCHEMA.ROUTINES

   INFORMATION_SCHEMA.ROUTINES is a system information schema view. This system view can be used to fetch all the details about the stored procedure like, name, definition / source code, database schema, created and modified date, etc.. You can use this view to search for stored procedure by its full or partial name and the stored procedures containing text. For now I’ll give you the query to find the stored procedure by its name. Later on in this article you will see about using INFORMATION_SCHEMA.ROUTINES to search stored procedure containing text.

   SELECT 
   	ROUTINE_NAME, 
   	ROUTINE_DEFINITION , 
   	ROUTINE_SCHEMA
   FROM 
   	INFORMATION_SCHEMA.ROUTINES 
   WHERE 
   	ROUTINE_TYPE='PROCEDURE' AND
   	ROUTINE_NAME LIKE '%Employee%' 
   

   

3. Sys.SysComments

   sys.syscomments is a system compatibility view.  This system view can be used to fetch the object id and the definition / source code of stored procedures, views, trigger, rule and other programmable objects within the database.

   SELECT 
   	OBJECT_NAME(id),
   	text
   FROM 
   	sys.syscomments 
   WHERE 
   	OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
   	OBJECT_NAME(id) LIKE '%Employee%' 
   ORDER BY OBJECT_NAME(id)
   

   

4. Sys.Sql_Modules

   sys.sql_modules is a system object catalog view. This system view can be used to fetch the user defined programmability object’s definition / source code.

   SELECT 
   	object_id, 
   	OBJECT_NAME(object_id), 
   	definition
   FROM 
   	sys.sql_modules
   WHERE 
   	OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND
   	OBJECT_NAME(object_id) LIKE '%Employee%'
   

   

Find Using Filter Settings In Object Explorer

Another simple way to find the stored procedure by its full or partial name is by using the filter settings option in SQL Server Management Studio Object Explorer. Below are the steps for using filter settings to find stored procedure.

1. In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
2. Expand the Programmability folder.
3. Right Click the Stored Procedures folder.
4. From the right-click menu, select Filter in the right-click menu.
5. Under filter, select Filter Settings. The filter settings window will pop up.
   
6. In the filter settings window against the property Name, you can choose one of the Operator from the list (Equals, Contains, Does not contain) and enter the search phrase (or stored procedure’s name or partial name) under Value and then click OK.
   
7. The list of stored procedures are filtered based on your filter criteria. Now expand the Stored Procedures folder. You can see only the stored procedures which fall under the filter criteria.

II. Find Stored procedure Containing Text Or Table Name

Just like searching for stored procedure by its name, you can also search and find stored procedures in SQL Server by the text or phrase or table name or column name used in the definition (source code). We can use the sys.procedures, INFORMATION_SCHEMA.ROUTINES, sys.syscomments or sys.sql_modules to get the list of stored procedures containing the text or table name or column name.

Below are few of the methods. In all the below methods replace the search condition Employee with your search phrase like table name, column name, view name or any other text in the stored procedure’s definition.

1. Sys.Procedures

   You can use the sys.procedures along with OBJECT_DEFINITION built-in meta data function to search within the definition or source code of the stored procedure.

   SELECT 
   	name,
   	OBJECT_DEFINITION(object_id)
   FROM 
   	sys.procedures 
   WHERE 
   	OBJECT_DEFINITION(object_id) LIKE '%Employee%'
   

   

2. INFORMATION_SCHEMA.ROUTINES

   SELECT 
   	ROUTINE_NAME, 
   	ROUTINE_DEFINITION , 
   	ROUTINE_SCHEMA
   FROM 
   	INFORMATION_SCHEMA.ROUTINES 
   WHERE 
   	ROUTINE_TYPE='PROCEDURE' AND
   	ROUTINE_DEFINITION LIKE '%Employee%' 
   

   

3. Sys.SysComments

   SELECT 
   	OBJECT_NAME(id),
   	text
   FROM 
   	sys.syscomments 
   WHERE 
   	OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
   	text LIKE '%Employee%' 
   ORDER BY OBJECT_NAME(id)
   

   

4. Sys.Sql_Modules

   SELECT 
   	object_id, 
   	OBJECT_NAME(object_id), 
   	definition
   FROM 
   	sys.sql_modules
   WHERE 
   	OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND
   	definition LIKE '%Employee%'
   

   

Related Article

• You may be interested in finding the last few executed queries in SQL Server.
• List All The UDFs (User Defined Functions).
• Search For Table.
• Case Sensitive Search.

Reference

• Stakeoverflow

If you know of any other methods to find stored procedure in SQL Server, please share it through the comments section below.