Simple SQL Server Search

Here is a quick way of searching your SQL Server database objects for a specific string. This statement will search stored procedures, functions, triggers and views for a string you provide in the tag.

DECLARE @SEARCHSTRING VARCHAR(255)
SELECT @SEARCHSTRING = '<search string>'
SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR', 'V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0

Note that the statement doesn't search through cells, just object definitions (excluding tables). Happy debugging!