general db search

Declare @Search varchar(255)

set @Search = 'IsThirdPartyProduct' --Set the search text here

DECLARE @SearchTxt varchar(255)

SET @SearchTxt = '%' + @Search + '%'

SELECT o.[name],
CASE
o.xtype
WHEN 'TR' THEN 'Trigger'
WHEN
'U' THEN 'User Table'
WHEN 'F' THEN 'Foreign Key'
WHEN 'C' THEN
'Check
Constraint'
WHEN 'UQ' THEN
'Index'
WHEN 'S' THEN 'System
Table'
WHEN 'D' THEN 'User Defined Data
Type'
WHEN 'PK' THEN 'Primary
Key'
WHEN 'FN' THEN 'Function'
WHEN 'TF' THEN
'Table Function'
WHEN 'V' THEN
'View'
WHEN 'P' THEN 'Stored
Procedure'
ELSE 'Other'
END
ObjType,
(SELECT TOP 1 SUBSTRING([Text],PATINDEX(@SearchTxt,
[Text]) - 25, 100) + '...'
FROM dbo.syscomments

WHERE [id] = c.[id]
AND
PATINDEX(@SearchTxt, [Text]) > 0) ObjText
FROM dbo.sysobjects
o
INNER JOIN dbo.syscomments c
ON o.[id] =
c.[id]
WHERE c.encrypted = 0
AND c.colid =
1
AND EXISTS( SELECT [id]
FROM dbo.syscomments
WHERE [text] LIKE
@SearchTxt
AND [id] =
c.[id])
ORDER BY ObjType, o.[name]