A stored procedure is a compiled SQL code that is saved in the database and can be reusable by calling this from a Client Application or another stored procedure. When there are tens and hundreds of stored procedures and if a programmer wants to find out if there is a stored procedure for some specific functionality or reference to a table, then the specific stored procedure can be searched by using the specific text search.

In this article, we will look into different ways of how to search text in stored procedures and what are the possible uses.

Search Text in Stored Procedure

A stored procedure generally contains different SQL queries like SELECT query, INSERT query, DELETE query, or an UPDATE query. From the stored procedure we can also call another stored procedure or another FUNCTION.

The Stored procedure can also RETURN a value after some processing using SQL Queries. When many stored procedures are created in a database for an application there could be a situation to search for a specific stored procedure by checking some specific keyword search like ‘Add New User‘ or ‘Customer‘ Or ‘Insert‘ and so on.

There are two ways to search for specific text from a stored procedure and display the stored procedure name with the contents in the stored procedure. INFORMATION_SCHEMA. ROUTINES is one of the options and another is to query Systems Tables and Views of type ‘P’ which means stored procedure. Let us look into these with examples.

Approach 1: Using the INFORMATION_SCHEMA.ROUTINES

Suppose our tasked to retrieving information about stored procedures in a MariaDB database that contain the term ‘Products‘ in their definition. we need to create and execute a SQL query to fetch the ROUTINE_NAME and ROUTINE_DEFINITION of such procedures from the INFORMATION_SCHEMA.ROUTINES table. Additionally, make sure that we are only retrieving information about procedures (not functions) by filtering with ROUTINE_TYPE='PROCEDURE'. Order the results alphabetically by ROUTINE_NAME.

Query:

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

Output:

Output for example 1

Explanation: The above query used to get text from Stored procedures, the INFORMATION_SCHEMA.ROUTINE object in database. This contains different items like Procedure and Function. In this query we are checking only for ‘Procedures‘ and from it the Name of the Procedure and the Text Content (SQL Code) inside the stored procedure. Also another LIKE filter is added to only stored procedures related to ‘Products‘ or having the keyword ‘Products‘. In the above example we can see the stored procedures with ‘products‘ table or keyword.

Approach 2: Text from Stored Procedures Using SQL_Modules

Suppose our task is to extract information about stored procedures in a SQL Server database that contain the term ‘Dynamic‘ in their definition. we need to create and execute a SQL query to fetch the Procedure_Name and Procedure_Text of such procedures from the sys.sql_modules and sys.objects tables. We are only retrieving information about procedures (not functions) by filtering with o.type = 'P'. Order the results alphabetically by Procedure_Name.

Query:

SELECT o.name AS Procedure_Name, m.definition as Procedure_Text
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id = o.object_id
WHERE m.definition LIKE '%Dynamic%' 
AND o.type = 'P'
ORDER BY o.name

Output:

Output for example 2

Explanation: In the above example the SQL_Modules and Sys. Objects database objects are used to get text from stored procedures. The above output shows stored procedures from the table with the Keyword ‘Dynamic‘ in it.

Approach 3: Using the SYSCOMMENTS Method

We will retrieve the names and definitions of stored procedures that contain the term ‘Dynamic‘ in their definition. we Ensure that the results only include procedures, and order them alphabetically by their names. We use the sys.sql_modules table to fetch the necessary information, filtering based on the procedure definition and type.

Query:

SELECT OBJECT_NAME(id) As Procedure_Name,S.text As Procedure_Text 
FROM SYSCOMMENTS S
INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id
WHERE S.TEXT LIKE '%Dynamic%'
AND O.type = 'P'

Output:

Syscomments and Sys.Objects example

Explanation: In the above query the Syscomments and Sys.Objects are used to get the stored procedure text. In the above output all text from stored procedures with the keyword ‘Dynamic‘ is fetched from the SYSCOMMENTS.

Approach 4: Multiple Search Filters

We will retrieve the names and definitions of stored procedures containing both ‘Insert‘ and ‘Students‘ in their definition. Ensure that the results only include procedures and order them alphabetically by their names. Use the INFORMATION_SCHEMA.ROUTINES table to extract the necessary information, filtering based on the procedure definition and type.

Query:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Insert%'  AND ROUTINE_DEFINITION LIKE '%Students%' 
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME

Output:

Multiple Serach text

Explanation: In the above output we can see stored procedures with multiple keywords and in this example stored procedure with ‘Student‘ table and ‘Insert‘ keyword.

Uses of Search Text in Stored Procedures

There can be many use cases for searching a specific text or keyword in Stored Procedures in a database. There can be times when there are large number of Stored Procedures already written and may not know what already exists and avoid duplication.

Below is some of common uses of Search Text in Stored Procedure:

• Useful to find a stored procedure name which contains a specific function or feature, so that no repetition of same functionality can be avoided or similar functionality can be written based on what is already written which saves time and effort.
• Helps to identify stored procedures which contains function or feature related to a specific purpose, so that if we need re-use part of the existing code or add additional functionality on top of it.
• Helps to find references of table names used in stored procedures, so that we can check for all stored procedures references for that specific table.

Conclusion

In this article, we have seen what is a stored procedure and how we can search for a specific stored procedure with specific keyword or having a particalar table name which could useful for a new developer who is looking for existing functionality in stored procedures which already exists. There are 3 methods we can do search in stored procedures in a database. We have also looked into the uses of search text in stored procedures.