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

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 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
FROM INFORMATION_SCHEMA.ROUTINES

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..!!!

Advertisements
Categories: TSQL Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: