Microsoft SQL Server - Find all the executed queries related with a table
In this tutorial, we’ll discuss on how to find all the executed queries related with a table in one database.
To get the all the queries that are executed in the database and related with a particular table, use below script.
SELECT SUBSTRING( deqt.TEXT,
(deqs.statement_start_offset/2)+1,
((CASE deqs.statement_end_offset
WHEN -1 THEN DATALENGTH(deqt.TEXT)
ELSE deqs.statement_end_offset
END - deqs.statement_start_offset)/2)+1
) AS ExecutedSqlQuery
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) deqt
WHERE text LIKE N'%IntendedTableName%'
AND text NOT LIKE '%sys.dm_exec_cached_plans%'
Alternatively, you can use below script as well.
SELECT [text] AS SqlQuery
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%IntendedTableName%'
AND text NOT LIKE '%sys.dm_exec_cached_plans%'
Change IntendedTableName to your table name that you are looking for.
- 111 reads