How to find a specific string from stored procedures, triggers and functions in database

There are several tables and views containing stored procedures, functions and triggers definitions.

Views –

SYS.SYSCOMMENTS, SYS.SQL_MODULES

Tables –

INFORMATION_SCHEMA.ROUTINES

I recommend the following query. It shows all SPs and Fns that contain any specific text. If we want to search only procedures, then filter in where clause "AND obj.Type='P'".

SELECT DISTINCT obj.name AS Object_Name,obj.type_desc 
FROM sys.sql_modules sm INNER JOIN sys.objects obj ON sm.object_id=obj.object_id 
WHERE sm.definition Like '%search text%'

If we want to search specific function or objects names, we can find dependencies using the following system stored procedure:

EXEC sp_depends @objname =searchobjectname

There is another way to find specific text using INFORMATION_SCHEMA.ROUTINES. But for me, it didn’t give me all the results.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%search text%'
--AND ROUTINE_TYPE='PROCEDURE' (for procedures only)

And also, SYS.SYSCOMMENTS view can have the same usage, but I got duplicates from some results.

SELECT SO.NAME, SC.TEXT
FROM SYS.SYSOBJECTS SO
JOIN SYS.SYSCOMMENTS SC ON SO.ID = SC.ID
WHERE SC.TEXT LIKE '%search text%' 
–-AND SO.TYPE = 'P'(for procedures only)

Hope this post helps you. Feel free to give your suggestions on this post. Until next post, cheers!!!

[edit]"Treat my content as plain text..." option disabled - OriginalGriff[/edit]