Query returning unused stored procedures
Whenever a proc is executed, you'll see the full CREATE PROCEDURE recorded in the DMVs, which SP_askbrent will be querying. 

Executions of each individual statement is also recorded, in sys.dm_exec_query_stats, and you can retrieve the exact text of what has been executed, by CROSS APPLY sys.dm_exec_sql_text(sql_handle) and applying the start and end offsets from the sys.dm_exec_query_stats DMV.

This is the code I use to identify what queries on my server are placing quite a load on the database in terms of IO, Duration and CPU.

It only works on SQL Server 2008+ though (unless you exclude the plan_hash and query_hash), but gives a nice indication of where I should start to look in terms of refactoring.

Each individual query within a stored procedure has the performance recorded (providing it completes and does not timeout)

SELECT
     deqs.plan_handle AS PlanHandle
    ,deqs.query_hash AS QueryHash
    ,DB_NAME(dest.dbid) AS DatabaseName
    ,OBJECT_SCHEMA_NAME(dest.objectid, dest.dbid) AS SchemaName
    ,OBJECT_NAME(dest.objectid, dest.dbid) AS ObjectName
    ,last_execution_time AS LastExecuted
    ,deqs.creation_time AS PlanCreationTime
    ,SUM(deqs.execution_count) AS Executions
    ,SUM(total_elapsed_time) AS SumDuration
    ,CAST(100.0 * SUM(total_elapsed_time)
                / SUM(SUM(total_elapsed_time)) OVER() AS NUMERIC(5,2)) AS PctDuration
    ,SUM(total_worker_time) AS SumCPU
    ,CAST(100.0 * SUM(total_worker_time)
                / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
    ,SUM(total_logical_reads + total_logical_writes + total_physical_reads) AS [SumIO]
    ,CAST(100.0 * SUM(total_logical_reads + total_logical_writes + total_physical_reads)
                / SUM(SUM(total_logical_reads + total_logical_writes + total_physical_reads)) OVER() AS NUMERIC(5,2)) AS PctIO
    ,SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                            ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                  ELSE deqs.statement_end_offset
                                END - deqs.statement_start_offset)/2)+1) AS statementtext      
    ,deqs.query_plan_hash AS QueryPlanHash                                    
  FROM sys.dm_exec_query_stats AS deqs 
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  WHERE deqs.last_execution_time &gt; DATEADD(dd, -1, GETDATE()) 
    -- Exclude where the dbid is NULL
    AND dest.dbid IS NOT NULL
    -- Exclude the non-user databases  
    AND DB_NAME(dest.dbid) NOT IN ('master', 'msdb')  
  GROUP BY 
        dest.text, deqs.statement_end_offset, deqs.statement_start_offset, deqs.plan_handle, deqs.last_execution_time, deqs.creation_time, 
          deqs.query_hash, dbid, dest.objectid, deqs.query_hash, deqs.query_plan_hash