Searching MSSQL Source Code for Multiple Strings

When you search the SQL source code of MSSQL database objects, you can use the catalog view sys.sql_modules in a statement like this:

select      sys.objects.name , sys.objects.type_desc
from sys.sql_modules
inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_idwhere sys.sql_modules.definition like '%MySearchString%'
order by type_desc, sys.objects.name

When you need to search for more than one string, you either have to repeat the search with every string, or select a UNION of all the searches.

An easier way is to collection all the search strings in a single on-the-fly table, and cross join this table for the search expression:

select      ids.name searchfor, sys.objects.name foundin, sys.objects.type_desc
from sys.sql_modules
inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
cross join (
    select 'Search1' as name
    union all select 'Text2'
    union all ... etc
 ) ids
where sys.sql_modules.definition like '%' + ids.name + '%'
order by type_desc, foundin

An example for such a search would be that after renaming a number of views or stored procedures, you need to find all references to them throughout all your TSQL code.

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: