Get all queries running against any specific table

/*

Get list of all queries hitting any specific tables

*/

SELECT DISTINCT TOP 100

ProcedureName = OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)

,SQLStatement = SUBSTRING(

sqlTxt.Text

,(QueryState.statement_start_offset/2)+1

,CASE QueryState.statement_end_offset

WHEN -1 THEN DATALENGTH(sqlTxt.text)

ELSE QueryState.statement_end_offset

END - (QueryState.statement_start_offset/2) + 1

)

,DiskReads = QueryState.total_physical_reads --- Disk reads

,MemoryReads = QueryState.total_logical_reads --–Logical Reads are memory reads

,ExecutionCount = QueryState.execution_count --Execution Count

,CPUTime = QueryState.total_worker_time  --CPU Time

,DiskWaitAndCPUTime = QueryState.total_elapsed_time

,MemoryWrites = QueryState.max_logical_writes

,DateCached = QueryState.creation_time

,DatabaseName = DB_Name(sqlTxt.dbid) --Database name

,LastExecutionTime = QueryState.last_execution_time

,cte.*

FROM sys.dm_exec_query_stats AS QueryState

CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) AS sqlTxt

CROSS APPLY sys.dm_sql_referenced_entities(

OBJECT_SCHEMA_NAME(sqlTxt.objectid) + '.' + OBJECT_NAME(sqlTxt.objectid)

, 'OBJECT'

) cte

WHERE sqlTxt.dbid = db_id() --Get detail for current database

AND cte.referenced_schema_name + '.' + cte.referenced_entity_name = 'dbo.tblEmployee'