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
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%'
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%'
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)
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.
- In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
- Expand the Programmability folder.
- Right Click the Stored Procedures folder.
- From the right-click menu, select Filter in the right-click menu.
- Under filter, select Filter Settings. The filter settings window will pop up.
- 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.
- 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.
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%'
SELECT ROUTINE_NAME, ROUTINE_DEFINITION , ROUTINE_SCHEMA FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_DEFINITION LIKE '%Employee%'
SELECT OBJECT_NAME(id), text FROM sys.syscomments WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 AND text LIKE '%Employee%' ORDER BY OBJECT_NAME(id)
SELECT object_id, OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%Employee%'
If you know of any other methods to find stored procedure in SQL Server, please share it through the comments section below.