SSMS Adv - Find a stored procedure

Here is a simple code to find a stored procedure.

You will need to update the 'DatabaseName' with which DB you would like to search from

the the 'definition' to a keyword. For example, you want to find the stored procedures that have "transaction" word, then that's what you will type-in.

Once you execute, it will show you a list of SPROCS that contains that keyword

USE DatabaseName

SELECT SPROC = CONCAT(SCHEMA_NAME(schema_id),'.',p.[name]), p.[type_desc], m.[definition]

FROM sys.procedures p

LEFT JOIN sys.sql_modules m ON p.object_id = m.object_id

WHERE 1=1

AND m.definition LIKE '%transaction%' --- a word that exist is the stored procedure

ORDER BY 1

Reference:

SYS OBJECT CATALOG VIEWS:

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql?view=sql-server-ver16