SQL – find by text

Many moons ago (at least 18 months ago), I had a stored procedure that let me search for text in DML in the databases. Yeah, so I could search for “Order” and it would find all mentions in SPs, functiosn etc.

Here is my new version:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
        SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, 
        ((CASE WHEN qs.statement_end_offset = -1 
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
        ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS [Individual Query] 
, qt.text AS [Parent Query] 
, DB_NAME(qt.dbid) AS DatabaseName 
, qp.query_plan 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
WHERE SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, 
        ((CASE WHEN qs.statement_end_offset = -1 
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
        ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) 
LIKE '@searchtext' 
and DB_NAME(qt.dbid) = @database