Search for a text string in a database's T-SQL

I believe the original author is Scott Burnell.


declare @textToSearchFor nvarchar(200)
declare @objectTypeToSearch char(1)
set @textToSearchFor = 'dimManagerCode'
-- use this as a sproc parameter
set @objectTypeToSearch = 'a'
-- use this as a sproc parameter (t=table,p=procedure,v=views,f=functions,a=all)
-- procedures
if (@objectTypeToSearch in ('a', 'p'))
begin
select
isnull('[' + s.[name] + '].[', '[') + p.[name] + ']' as ProcName --objectName.'
from
sys.procedures p
inner join sys.sql_modules m
on p.object_id = m.object_id
left join sys.all_objects obj
on p.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
m.definition like '%' + @textToSearchFor + '%'
order by
ProcName
end
-- tables
if (@objectTypeToSearch in ('a', 't'))
begin
select
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as TableName --objectName
from
INFORMATION_SCHEMA.COLUMNS c
where
c.COLUMN_NAME like '%' + @textToSearchFor + '%'
order by
TableName
end
-- views
if (@objectTypeToSearch in ('a', 'v'))
begin
select
isnull('[' + s.[name] + '].[', '[') + p.[name] + ']' as ViewNameDefinition --objectName
from
sys.views p
inner join sys.sql_modules m
on p.object_id = m.object_id
left join sys.all_objects obj
on p.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
m.definition like N'%' + @textToSearchFor + N'%'
select
isnull('[' + s.[name] + '].[', '[') + obj.[name] + ']' as ViewNameOutputColumn --objectName
from
sys.all_columns c
inner join sys.objects obj
on c.object_id = obj.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
obj.[type] = 'v'
and c.[name] like '%' + @textToSearchFor + '%'
select distinct
'[' + VIEW_SCHEMA + '].[' + VIEW_NAME + ']' as ViewName,
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '].[' + COLUMN_NAME + ']'as UsesTableColumn
from
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
where
COLUMN_NAME like '%' + @textToSearchFor + '%'
order by
ViewName
end
-- functions
if (@objectTypeToSearch in ('a', 'f'))
begin
select
isnull('[' + s.[name] + '].[', '[') + obj.[name] + ']' as FunctionName --objectName
from
sys.objects obj
inner join sys.sql_modules m
on obj.object_id = m.object_id
left join sys.schemas s
on obj.schema_id = s.schema_id
where
obj.[type] = 'FN'
and m.definition like '%' + @textToSearchFor + '%'
order by
FunctionName
end