« All articles

Searching stored procedures containing specific strings

Sometimes we might need to find procedures that contain certain strings in order to know which procedures affect specific processes in our system and in case of changes to those processes which procedures would we need to modify, therefore we need a simple way that will let us easily find all procedures that contain certain strings.

The following queries (that return the same results) will help you find all procedures that contain a certain string (“Foo” in this example):

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

2. SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%Foo%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

3. SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%Foo%'

So, as we can see, it is simple to find the names of all procedures that contain a certain string in their code and save expensive development time.

Leave a Reply

Your email address will not be published.