How to find a text inside SQL Server procedures / triggers?

You can search within the definitions of all database objects using the following SQL:

SELECT
o.name,
o.id,
c.text,
o.type
FROM
sysobjects o
RIGHT JOIN syscomments c
ON o.id = c.id
WHERE
c.text like '%text_to_find%'

This one i tried in SQL2008, which can search from all the db at one go.

Create table #temp1
(ServerName varchar(64), dbname varchar(64)
,spName varchar(128),ObjectType varchar(32), SearchString varchar(64))

Declare @dbid smallint, @dbname varchar(64), @longstr varchar(5000)
Declare @searhString VARCHAR(250)

set @searhString='firstweek'

declare db_cursor cursor for
select dbid, [name]
from master..sysdatabases
where [name] not in ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs')



open db_cursor
fetch next from db_cursor into @dbid, @dbname

while (@@fetch_status = 0)
begin
PRINT 'DB='+@dbname
set @longstr = 'Use ' + @dbname + char(13) +
'insert into #temp1 ' + char(13) +
'SELECT @@ServerName, ''' + @dbname + ''', Name
, case when [Type]= ''P'' Then ''Procedure''
when[Type]= ''V'' Then ''View''
when [Type]= ''TF'' Then ''Table-Valued Function''
when [Type]= ''FN'' Then ''Function''
when [Type]= ''TR'' Then ''Trigger''
else [Type]/*''Others''*/
end
, '''+ @searhString +''' FROM [SYS].[SYSCOMMEnTS]
JOIN [SYS].objects ON ID = object_id
WHERE TEXT LIKE ''%' + @searhString + '%'''

exec (@longstr)
fetch next from db_cursor into @dbid, @dbname
end

close db_cursor
deallocate db_cursor
select * from #temp1
Drop table #temp1

SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Your Text%'

Just wrote this for generic full outer cross ref

create table #XRefDBs(xtype varchar(2),SourceDB varchar(100), Object varchar(100), RefDB varchar(100))

declare @sourcedbname varchar(100),
@searchfordbname varchar(100),
@sql nvarchar(4000)
declare curs cursor for
select name
from sysdatabases
where dbid>4
open curs
fetch next from curs into @sourcedbname
while @@fetch_status=0
begin
print @sourcedbname
declare curs2 cursor for
select name
from sysdatabases
where dbid>4
and name <> @sourcedbname
open curs2
fetch next from curs2 into @searchfordbname
while @@fetch_status=0
begin
print @searchfordbname
set @sql =
'INSERT INTO #XRefDBs (xtype,SourceDB,Object, RefDB)
select DISTINCT o.xtype,'''+@sourcedbname+''', o.name,'''+@searchfordbname+'''
from '+@sourcedbname+'.dbo.syscomments c
join '+@sourcedbname+'.dbo.sysobjects o on c.id=o.id
where o.xtype in (''V'',''P'',''FN'',''TR'')
and (text like ''%'+@searchfordbname+'.%''
or text like ''%'+@searchfordbname+'].%'')'
print @sql
exec sp_executesql @sql
fetch next from curs2 into @searchfordbname
end
close curs2
deallocate curs2
fetch next from curs into @sourcedbname
end
close curs
deallocate curs

select * from #XRefDBs

You can find it like

SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%User%'

It will list distinct stored procedure names that contain text like 'User' inside stored procedure. [More info][1]


[1]:

[To see links please register here]