This is closely related to my last post, where we were searching for text in the data itself, but now we’re just looking to see if the text exists in the statements that create views or stored procedures. This has been tested only on SQL Server 2008 R2. If you do not have permission on the database to see the definitions of views or stored procedures, then you will get zero rows returned. Otherwise, the names of the views and stored procs containing the text in @TestValue will be listed here, along with their respective CREATE statements. This script should run much faster than the one in the above-referenced post since no cursors are being used here.
-- Set database to search
USE dbname
GO
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here
-- no other code below changes
DECLARE @TestValue VARCHAR(MAX)
-- If characters in @TestValue need to be escaped, use backslash
SET @TestValue = 'text to search for'
------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT
o.name AS [Object_Name],
o.type_desc,
m.[definition]
FROM sys.sql_modules m
INNER JOIN
sys.objects o
ON m.object_id = o.object_id
WHERE m.definition LIKE '%' + @TestValue + '%' ESCAPE '\';