Query to check for a text in All SQL Server 2000 sources in a database

  • Article

If you ever felt a need to perform a query to check for a particular text instance in all sources (SPs, functions, triggers et al) in your SQL 2000 db, chances are you might have already written this little goodie. But if you haven't and if you need some quick script to get this done for you, here's one I have been using for quite sometime and I gotta say, it works like a sweet charm! Best of luck!

 

 
SELECT [number], [TEXT], A.name

FROM syscomments B, sysobjects A

WHERE A.[id]=B.[id]

and TEXT like '%your text%'

ORDER BY [number]

GO

 
And here's the sql job part -

 

 
select S.name

from msdb.dbo.sysjobs J

inner join msdb.dbo.sysjobsteps
S

ON J.job_id=S.job_id

where command like ' %your text%'