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 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]