nPath Ltd.

You can use this query to search your entire server for a string. It will search views, stored procs, triggers, everything.

All the sql text in your database is stored in system tables under each database. There are a few hardships to overcome in order to search.

1.) Text is split into 4000 character chunks. So every 4k chunk has a colID to note its position in the stack of rows .  The names are stored in sysobjects and the text is stored in syscomments. The issue is when your search hits in the second chunk. You need to pull in all chunks when any one finds a hit.

2.) Each database has it’s own system tables. For one search to check every database’s syscomments you need to loop. My example solution didn’t use a cursor.

I pulled a list of database names out of master.dbo.sysdatabases and put it in a temp table. That is what I used for my loop. I  just jet the top row, work it, delete it, and get the next one from the top.

In the loop I execute sql constructed dynamically based on the database name of in the current loop iteration. It searches for all object Ids that have text matching the search term. Then gets all chucks for that object.  To help me narrow down the search I also search on object type. V for view, p for proc  etc…Leave type ” to get all types.

If you paste this in your query window and set the search strings you should get results.  I clipped the text to 3990 because I kept getting errors that the row was too large. I should probably look into it but this will do for now.

I’m dealing with MSSql Server 2000 here and this works.  I plan to make this a stored proc and call it from asp but right now it’s 5:00pm on Friday and this can wait ’till Monday.

Create Table #dblist (NAME VARCHAR(80))

createtable #results (name nvarchar(80),text varchar(4000))

insert into #dblist

exec(‘select name from master..sysdatabases order by name’)

declare @ObjectType as nvarchar(80)
declare @search as nvarchar(80)

set @search = ‘update’ –change this
set @ObjectType=‘p’  –searches only procedures
–set to ” for all types

declare @dbname as nvarchar(80)

set @dbname= (select top 1 name from #dblist)

while @dbname<>”

begin

insert into #results
exec(
‘select id ‘ +
‘into #found ‘ +
‘from ['+@dbname+']..syscomments ‘+
‘where [text] like ”%’+ @search +‘%” ‘ +

‘select ”’+ @dbname+‘:” + so.name as name, left(sc.text, 3990) ‘ +
‘from [' + @dbname + ']..syscomments sc ‘ +
‘inner join [' + @dbname + ']..sysobjects so on sc.id= so.id ‘ +
‘where sc.id in (select id from #found) ‘+
‘and xtype like ”%’ + @ObjectType+ ‘%” ‘+
‘order by name, colid ‘+
‘drop table #found’

)

delete from #dblist where name= @dbname

set @dbname= (select top 1 name from #dblist)

end

select * from #results

drop table #dblist

drop table #results