Akeesoft

Akeesoft Logo

Search Text In Stored Procedure
In SQL Server

A stored procedure is a SQL code with step by step instruction available in the database, when required to be used stored procedure , instructions will be compiled and saved in the database and the recompiled stored procedure can be used multiple times based on the need as stored procedure can be reused . 

When dealing with numerous stored procedures, a programmer might need to locate a specific one based on its functionality or its references to particular tables. This can be achieved through search text in stored procedure in SQL Server.

In this article, we will explore various methods for searching text and discuss ways to find out the same.

Stored Procedure in SQL Server

Stored Procedure(SP) is developed by using the with commands or queries like SELECT,INSERT,UPDATE and DELETE Queries.If required we have feasibility to call another stored procedure and pass the output as per the requirement.

As we are using multiple sql queries in the procedure ,Output of the queries used in the Stored Procedure(SP) will returning the output and the output can used based on the development need.

As part of development multiple stored procedures are created to achieve the need of requirement, Out of multiple stored procedures find the required keyword will be a hectic task and time consuming work to search and find the required keyword, Like to search the keyword like Login, Count or Return etc.,

Out of multiple stored procedures to find the required keyword ,Can use four options for doing search of text in stored procedures.

Using SQL_Modules

To search for the keyword “Purge” in the stored procedure name can use below script with the help of sql_modules and sys.objects , by creating an inner join and filtering the module definition with keyword we want ,here example “Purge” and by by searching for Object Type as P means only filtering Procedure or Stored Procedure.

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

Using Information_Schema

Routine_definition like ‘%Login%’ is the command used to search the stored procedure with text as “Login”. routine_type=’procedure’ this is the command to search only the Store Procedure.

With information_scheme.routines, can searches only the first 4000 characters of the procedure

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

Syscomments Method

With the method of syscomments ,we can search the stored procedure by using below query.If we want to search the same keyword of Purge using syscomment method ,will inner join syscomments and sysobjects on ID and filter with the keyword purge and making sure object type as P means procedure or stored procedure.

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

With Sysprocedure

We can find the text of stored procedure by using the system procedure call sys.procedure.
Get the data from sys.procedure system procedure and pass the filter in the ObjectID in the Object Definition and password the keyword to search the stored procedure.

There are multiple other ways which text in Stored Procedure can be searched. These methods are mostly useful during any troubleshooting and if there are multiple stored procedure already deployed.

SELECT
OBJECT_NAME(object_id) as Name,
OBJECT_DEFINITION(object_id) as Body
FROM
sys.procedures WHERE
OBJECT_DEFINITION(object_id) LIKE '%[[]Purge%'

Uses Of Search Text

If we have multiple stored procedures developed and live on the production and you have requirement to develop a new stored procedure, Out of many sp’s you may not remember ,If you continue working on new stored procedure then that will be a duplicate work. To avoid such duplicate work searching the sp text will help where it is available or not.

Can avoid rework or duplicate work by using search text option.

Can check functions or code used for the stored procedure before developing a new one.

Searching of text is useful while performing any trouble shooting ,to identify the code and related information on the sp or functions.

Conclusion

In this post ,recapped what is Stored Procedure, and ways to search text in the stored procedures. Majorly focusing on the SQL Scripts required to search Stored Procedure text   in SQL Server