Search Text in all SQL Server Objects

Many time you come through a situation where you want to Search a text in SQL Objects such as table, stored procedure, views, triggers, etc.., but we don't get when required.

Below SQL Script will search any text assigned to @Search variable in all Tables, Users, Views, Functions, Stored Procedures, Primary Key, Foreign Key etc..


Script to search text in all SQL Server objects:

DECLARE @Search varchar(255)
SET @Search='xsp_get' -- GIVE YOUR SEARCH TEXT
SELECT DISTINCT
LEFT(so.name, 100) AS Object_Name,
LEFT(
CASE so.type
WHEN 'U' THEN 'Table - User'
WHEN 'S' THEN 'Table - System'
WHEN 'P' THEN 'Stored Procedure'
WHEN 'V' THEN 'Table - View'
WHEN 'TR' THEN 'Trigger'
WHEN 'C' THEN 'Constraint - Check'
WHEN 'D' THEN 'Default'
WHEN 'K' THEN 'Key - Primary'
WHEN 'F' THEN 'Key - Foreign'
WHEN 'L' THEN 'Log'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication Filter stp'
ELSE '<<UNKNOWN ' + so.type + '>>'
END
,25) AS Object_Type
FROM syscomments sc
INNER JOIN sysobjects so ON so.id = sc.id
WHERE text Like '%'+@Search+'%'
ORDER BY 2,1