T-SQL – Get a Historical Record of SQL Statements Hitting a Table

Today I’ll review two useful queries that you can use to get information from SQL Server about what SQL statements are hitting one or more of your database tables.

If you are interested in further T-SQL tips and tricks, please have a look through related articles I have written.

The first query will check SQL Server’s procedure cache to return a historical list of queries affecting a particular table.

This query checks the sys.dm_exec_query_stats system view and the sys.dm_exec_sql_text table valued function. Note that these exist in SQL Server 2005 and up, so if you are running SQL Server 2000 you will have to use a different query.

Also because these are system tables, you will need to be logged into SQL Server Management Studio (SSMS) with an account that has sufficient access permissions.

Here is the query:

 SELECT
 qryStats.last_execution_time AS [Time]
 ,qryText.TEXT AS [Query]
 ,DB_NAME(qryText.[dbid]) AS [Database]
 ,OBJECT_NAME(qryText.[objectid]) AS [TableName]
 FROM sys.dm_exec_query_stats AS qryStats
 CROSS APPLY sys.dm_exec_sql_text(qryStats.sql_handle) AS qryText
 WHERE qryText.TEXT LIKE '%TableName%'
 ORDER BY qryStats.last_execution_time DESC

Running this query will show you in reverse chronological order what SQL was run against a particular table on your database server.

If you are using stored procedures then you’ll see that rather than showing the EXEC statement, SQL Server will have logged the CREATE statement for the stored procedure itself. For our purposes this is useful since it allows us to also find stored procedures that are having an effect on the table that we are interested in.

The downside of this query is that it will not show you what user account is running each query.

Monitoring Statements in Real-Time

You can also use a T-SQL query to monitor currently running queries against your database as they happen using the dynamic management view (DMV) sys.dm_exec_requests  together with the table-valued function sys.dm_exec_sql_text.

Here is an example:

SELECT
 z.start_time
 ,z.command
 ,z.status
 ,x.text
 ,USER_NAME (z.user_id) [User Name]
 ,DB_NAME(z.database_id) [Database]
 ,OBJECT_NAME(x.objectid) [Table Name]
 FROM sys.dm_exec_requests z
 CROSS APPLY sys.dm_exec_sql_text(z.sql_handle) x
 WHERE x.text LIKE '%TableName%'
 ORDER BY z.start_time DESC

As you can see, this information is quite detailed, but since it is transitory you may want to set up a routine (ie: a scheduled job) to store the information from this query to a database table. In this way you will have detailed log information that is significantly more searchable than a SQL Profiler Trace or manually browsing the SQL Server Logs.

Alternately since we are also gathering the user ID, you could filter your results by a particular user that you are interested in monitoring.

Alternate ways of getting detailed log information about events happening to your tables are:

• Adding custom triggers to your table to save access information
• Running a SQL Server Profiler trace
• Checking through SQL Server’s log files (under: Your Server’s Name –> Management –> SQL Server Logs)
• Running a server-side trace
• Using Extended Events if you are running SQL Server 2012