Procurando uma string em todas as procedures, views, functions, triggers etc

Aqui apresento uma solução para o problema proposto:

CREATE FUNCTION [dbo].[Search]
(   
@str VARCHAR(100)
) RETURNS TABLE RETURN   

SELECT  DISTINCT   
     s.name AS SchemaName,
     o.name AS ObjectName,
     o.type_desc AS ObjectType   

FROM sys.objects o    INNER JOIN syscomments c
    ON c.Id = o.object_id    INNER JOIN sys.schemas s ON s.schema_id = o.schema_id   
WHERE     is_ms_shipped = 0 AND c.text like '%' + @str + '%'

Ao usar esta function, podemos pesquisar por  uma string em todos os objetos do banco e aplicar um filtro para extrair linhas específicas. 

Vejamos alguns exemplos:

a) SELECT * FROM Search('BusinessEntityID')

b) SELECT * FROM Search('BusinessEntityID')  WHERE ObjectType = 'VIEW'
c) SELECT * FROM Search('BusinessEntityID')
    WHERE ObjectType = 'SQL_STORED_PROCEDURE'  AND SchemaName = 'HumanResources'

Abuse e use...