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%'
    

    SQL Find Stored procedure By Name

  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%' 
    

    SQL Find Stored procedure by Name

  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)
    

    Find Stored Procedure By Name

  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%'
    

    SQL Find Stored Procedure By Query

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.
    SQL Find Stored Procedure Using Filter Settings
  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.
    SQL Find Stored Procedure Using Filter Settings
  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%'
    

    SQL Find Stored procedure Containing Text

  2. INFORMATION_SCHEMA.ROUTINES

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

    SQL Find Stored procedure Containing Text

  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)
    

    SQL Dind Stored procedure Containing Text

  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%'
    

    SQL Find Stored Procedure Containing Text

Related Article

Reference

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


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.