Search stored procedure or view code for string

You might find the following two SQL queries handy. They find a string within the SQL code of a stored procedure or view.

-- Stored Procedure – looking for string ‘TEST’

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%TEST%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

-- View – looking for string ‘TEST’

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%TEST%'
AND OBJECTPROPERTY(id, 'IsView') = 1
GROUP BY OBJECT_NAME(id)