Sometimes you'd like to find all occurrences of a piece of text in every stored procedure, trigger, etc., possibly to replace it with a new value. The script below will do just that; it returns the database name, the object name, and the object type. Note that it works for both SQL Server 2000 and post-SQL Server 2000 versions.
One thing to note is that the @TextToFind uses the LIKE operator with its ESCAPE argument set to the dollar sign symbol. I added the ESCAPE argument just to make it easier to search for text such containing the underscore character, such as 'My_HardToFind_Thing". Of course, if you're searching for text with a dollar sign symbol in it, you'll want to change the ESCAPE argument to something else.
(The script below can also be run in CMS, which will allow you to also return results across server groups.)
-- Simply set the text you want to look for here.
declare @TextToFind nvarchar(4000)
set @TextToFind = 'THIS IS MY TEXT TO FIND'
declare @Sql nvarchar(4000)
set @Sql =
'use [?]
declare @DatabaseName sysname
declare @ProcName sysname
declare @ProcType nvarchar(60)
if left(convert(nvarchar, serverproperty(''productversion'')), 1) <> ''8''
begin
declare cur cursor local fast_forward for
select distinct ''?'' as ''Database Name''
, o.name as ''Object Name''
, o.type_desc as ''Object Type''
from sys.sql_modules m
join sys.objects o
on m.object_id = o.object_id
where m.definition like ''%' + @TextToFind + '%'' escape ''$''
open cur
fetch next from cur into @DatabaseName, @ProcName, @ProcType
while @@fetch_status = 0
begin
print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
fetch next from cur into @DatabaseName, @ProcName, @ProcType
end
close cur
deallocate cur
end
else
begin
declare cur cursor local fast_forward for
select distinct ''?'' as ''Database Name''
, object_name(c.id) as ''Object Name''
, o.xtype as ''Object Type''
from syscomments c
join sysobjects o
on c.id = o.id
where c.[text] like ''%' + @TextToFind + '%'' escape ''$''
and objectproperty(c.id, ''isprocedure'') = 1
open cur
fetch next from cur into @DatabaseName, @ProcName, @ProcType
while @@fetch_status = 0
begin
print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
fetch next from cur into @DatabaseName, @ProcName, @ProcType
end
close cur
deallocate cur
end
'
exec sp_msforeachdb @Sql
