≡ Menu
Deep in the Code

Searching for Text in the Create Statements of Views and Stored Procedures in SQL Server

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 '\';

About the author: I solve problems. Solutions Architect / Senior Software Engineer / Business Analyst / Full-Stack Developer / IT Generalist