How to find matching stored procedures containing a text in MS SQL Server

Screenshot from the tutorial

How to Find Matching Stored Procedures Containing a Text in MS SQL Server

Finding stored procedures that contain specific text in SQL Server can be crucial for various tasks, such as debugging, refactoring, or understanding database logic. In this post, we will explore how to efficiently locate stored procedures that contain a specific text string using a simple SQL query.

Understanding Stored Procedures in SQL Server

Stored procedures are precompiled collections of one or more SQL statements that can be executed as a single unit. They can accept parameters, return values, and provide a way to encapsulate business logic within the database. However, as the number of stored procedures grows, it can become challenging to track down where certain logic is implemented, especially if you need to find procedures containing specific keywords or phrases.

Using INFORMATION_SCHEMA to Find Stored Procedures

SQL Server provides system views that allow you to query metadata about database objects. One such view is the INFORMATION_SCHEMA.ROUTINES view, which contains information about all stored procedures and functions in a database.

Query to Search for Text in Stored Procedures

To find stored procedures that contain specific text, you can execute the following SQL query:

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

Breaking Down the Query

• ROUTINE_NAME: This column returns the name of the stored procedure.
• ROUTINE_TYPE: This specifies the type of routine, which in this case is 'PROCEDURE'.
• ROUTINE_DEFINITION: This contains the actual SQL code of the stored procedure.

The WHERE clause filters the results to include only stored procedures and checks if the ROUTINE_DEFINITION contains the text you are searching for. Replace YourTextHere with the actual text string you wish to find.

Example Query

Suppose you want to find all stored procedures that contain the word "Invoice". The query would look like this:

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

Important Notes

1. Case Sensitivity: The comparison is case-insensitive by default in SQL Server unless your database collation is set to be case-sensitive.

2. Limitations: The ROUTINE_DEFINITION column might be limited in size in some SQL Server versions (e.g., it can only show up to 4000 characters). If you need to search longer stored procedures, consider using the sys.sql_modules view instead.

Alternative Using sys.sql_modules

If you find that the previous method doesn't meet your needs, you can use the sys.sql_modules table. Here’s an alternative query:

SELECT 
    OBJECT_NAME(object_id) AS ProcedureName, 
    definition 
FROM 
    sys.sql_modules 
WHERE 
    definition LIKE '%YourTextHere%'

This query retrieves the stored procedure name and its definition directly from the sys.sql_modules system catalog view.

Conclusion

Finding stored procedures that contain specific text in SQL Server doesn't have to be a tedious process. By utilizing the INFORMATION_SCHEMA.ROUTINES or sys.sql_modules views, you can quickly identify the relevant procedures, facilitating easier debugging and maintenance of your database.

Feel free to adapt the queries to suit your specific needs, and happy querying!

Another view from the tutorial

---

Connect with SkillBakery Studios

Explore more tutorials, tools, and resources:

• skillbakery.com
• Facebook
• X (Twitter)
• YouTube Channel

Posted by SkillBakery Studios