SQL query to search a string in database Schema

Suppose to search an author name 'Steve' string in entire database schema then use following query.

Change the value of variable @Search according to your search.

DECLARE @Search VARCHAR(100)
set @Search='Steve'
SELECT o.name[Object Name],
CASE O.xtype  WHEN 'P' THEN 'Stored procedure'
			WHEN 'RF' THEN 'Replication filter stored procedure'
			WHEN 'TR' THEN 'Trigger'
			WHEN 'FN' THEN 'Scalar function'
			WHEN 'IF' THEN 'In-lined table-function' 
			WHEN 'IT' THEN 'Internal table'
			WHEN 'TF' THEN 'Table function' 
			WHEN 'V' THEN 'View' 
			WHEN 'X' THEN 'Extended stored procedure'
ELSE ' ' END[Object Type]
FROM syscomments c
INNER JOIN sysobjects o on o.id=c.id
WHERE text like '%' +@Search +'%'
ORDER BY [Object Name],[Object Type]