Get all stored procedures that contain specific text in SQL Server

Today I had to find all procedures where two table were referenced. Also I had to Prioritise them in order to work on the most used procedure first.

After searching the web I found a few snipets of code here and there and put the following together.

Note: The query won’t return procedures that have not been executed since the last restart.

Here is part of the join clause, adapt as neccessary.

A.ROUTINE_DEFINITION LIKE  ''%AccountManagement%''

OR

A.ROUTINE_DEFINITION LIKE  ''%LTCDB_admin.tbl_Sales_Contacts%''

Here is the complete query

DECLARE @strSQL VARCHAR(4000)
SET @strSQL = '
 DECLARE @approx_server_start_utc_datetime INT
 SELECT
 @approx_server_start_utc_datetime = ( ( ( dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00 ) / 60.00 ) / 24.00
 FROM
 ?.sys.dm_io_virtual_file_stats(1 , 1)
SELECT
 D.name EXEC_CONTEXT
 ,SPECIFIC_CATALOG
 ,SPECIFIC_SCHEMA
 ,SPECIFIC_NAME
 ,ROUTINE_DEFINITION
 ,CREATED
 ,LAST_ALTERED
 ,REFCOUNTS
 ,USECOUNTS
 ,@approx_server_start_utc_datetime UP_TIME_DAYS
 FROM
 [ovott].INFORMATION_SCHEMA.ROUTINES A
 INNER JOIN [ovott].sys.objects B
 ON B.Name = A.ROUTINE_NAME
 AND A.ROUTINE_DEFINITION LIKE ''%AccountManagement%''
 AND A.ROUTINE_TYPE = ''PROCEDURE''
 AND B.Type = ''P''
 LEFT OUTER JOIN (
 SELECT
 objectid
 ,refcounts
 ,usecounts
 ,dbid
 FROM
 [ovott].sys.dm_exec_cached_plans cp
 CROSS APPLY [ovott].sys.dm_exec_query_plan(cp.plan_handle) qp
 WHERE
 objtype = ''PROC''
 ) C
 ON C.OBJECTID = B.OBJECT_ID
 INNER JOIN sys.databases D
 ON D.database_id = C.dbid'
DROP TABLE  #tmpResults
CREATE TABLE #tmpResults
 (
 EXEC_CONTEXT VARCHAR(255)
 ,EXIST_CATALOG VARCHAR(255)
 ,SPECIFIC_SCHEMA VARCHAR(255)
 ,SPECIFIC_NAME VARCHAR(255)
 ,ROUTINE_DEFINITION VARCHAR(MAX)
 ,CREATED DATETIME
 ,LAST_ALTERED DATETIME
 ,REFCOUNTS INT
 ,USECOUNTS INT
 ,UP_TIME_DAYS INT
 )
INSERT  INTO #tmpResults
 EXEC sp_msforeachdb
 @strSQL
SELECT
 *
FROM
 #tmpResults
ORDER BY
 EXIST_CATALOG
 ,USECOUNTS DESC