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 following query it shows all SPs and Fns that contains any specific text.If we want to search only procedure 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 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
WHERE ROUTINE_DEFINITION LIKE ‘%search text%’
AND ROUTINE_TYPE=’PROCEDURE’ (for procedures only)
And also SYS.SYSCOMMENTS view can use to 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%’ –SO.TYPE = ‘P'(for procedures)
Hope this post helps you.Feel free to give your suggestion on this post.Until next post cheers..!!!