Get top SQL Queries that aren't stored procs/functions/triggers Afternoon,

I need way to get the query stats for queries that aren't procedures/functions/triggers. 

sys.dm_exec_query_stats only appears to contain these, i need to gather similar stats that are in this dmv but for just normal queries submitted by ssms/EF/Web etc.

This is what i used and it returns no results. (not the most efficient but just a quick proof of concept)

SELECT txt.text
   last_execution_time,
   execution_count,
   total_elapsed_time,
   last_elapsed_time,
   min_elapsed_time,
   max_elapsed_time
   FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS txt 
   WHERE
   txt.text NOT LIKE '%CREATE PROC%' 
   AND txt.text NOT LIKE '%CREATE FUNC%'
   AND txt.text NOT LIKE '%CREATE TRIGG%'
   AND txt.dbid = DB_ID('$(DB)')


I know there are several queries submitted regularly from client apps that don't use stored procs and take a long time to execute. I would have thought SQL keeps a record of these somewhere.
There's an easy (and free!) way to do that using sp_BlitzCache (full disclosure, I contribute to this OSS).

You can run it with the @QueryFilter option (which can also get only stored procs, and only functions (but functions are only 2016+).

EXEC master.dbo.sp_BlitzCache @QueryFilter = 'statement'

That will give you your top 10 statements by overall CPU. If you want to order by other metrics, you can use the @SortOrder parameter to go by reads, duration, executions, and more.

Hope this helps!