CODEWITHSUNDEEP
sqlsql-server
Amit Arora
Amit Arora

Reputation: 175

List of Table names from SQL queries

I am trying to look for a way to get the list of all the table names used in various queries executed in a SQL Server database in last 30 days.

I have the queries and like to know if we can programmatically (rather than manually parsing it ) get the list of table names out of it or can get the details from any SQL Server system table.

Upvotes: 0

Views: 412

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

Assuming the server hasn't been restarted in the last 30 days, a potentially more accurate way to determine what tables have been queried (in the event that your collection of queries is incomplete):

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

;WITH agg AS
(
    SELECT 
        [object_id],
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    [Schema] = OBJECT_SCHEMA_NAME([object_id]),
    [Table_Or_View] = OBJECT_NAME([object_id]),
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT [object_id], last_user_seek, NULL FROM agg
    UNION ALL
    SELECT [object_id], last_user_scan, NULL FROM agg
    UNION ALL
    SELECT [object_id], last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id])
ORDER BY [Schema],[Table_Or_View];

If you want to rely on the queries you've collected, there is no magic system table that maps queries to table(s). So, not sure exactly where are how these queries you've collected are stored, but you could attempt to parse the table or index names out of the plan XML using sys.dm_exec_cached_plans and sys.dm_exec_sql_text. This will not be simple and, unless you have more memory than you could possibly need, it is highly unlikely that all of the plans for 30 days worth of queries are currently in the plan cache.

Upvotes: 1

Related Questions