Find All Query

SET @searchString = ‘status’

SELECT rs.[Name]

, rs.[Type]

, rs.[xtype]

, COUNT([TextMatch]) as ‘Number of References’

FROM   (

SELECT so.[Name]

, so.[Type]

, so.[xtype]

, so.[Name] as ‘TextMatch’

FROM   sysobjects so

WHERE  so.[name] IS NOT NULL

AND    so.[Name] LIKE ‘%’ + @searchString + ‘%’

AND    (   so.[Type] = ‘U’   — User Table

OR so.[Type] = ‘P’   — Stored Procedure

OR so.[Type] = ‘V’   — View

OR so.[Type] = ‘FN’  — User Defined Function

OR so.[Type] = ‘TR’  — Trigger

OR so.[Type] = ‘K’   — Primary Key

OR so.[Type] = ‘F’   — Foreign Key

OR so.[Type] = ‘DF’  — Default Value

)

UNION

SELECT so.[Name]

, so.[Type]

, so.[xtype]

, sc.[text] as ‘TextMatch’

FROM   sysobjects so

JOIN   syscomments sc

ON     so.[id] = sc.[id]

WHERE  so.[name] IS NOT NULL

AND    sc.[text] LIKE ‘%’ + @searchString + ‘%’

AND    (   so.[Type] = ‘U’   — User Table

OR so.[Type] = ‘P’   — Stored Procedure

OR so.[Type] = ‘V’   — View

OR so.[Type] = ‘FN’  — User Defined Function

OR so.[Type] = ‘TR’  — Trigger

OR so.[Type] = ‘K’   — Primary Key

OR so.[Type] = ‘F’   — Foreign Key

OR so.[Type] = ‘DF’  — Default Value

)

) rs

GROUP BY rs.[Name], rs.[Type], rs.[xtype]

ORDER BY rs.[Type], rs.[name]