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