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