SQL Server: How get a list of tables accessed by a SELECT statement

Question

I have many hundred SQL select statements stored in the database table in a nvarchar column.

For each select statement, I need to find out what tables they read from. I need to do this programmatically (e.g. with T-SQL) and store the list of accessed tables in a database table.

I started doing this by calling the stored procedure sp_describe_first_result_set. It works only partially.

For example:

EXEC sp_describe_first_result_set  
  @tsql = 'SELECT 
               a.code, b.customer_name
           FROM table_a a 
           INNER JOIN table_b b ON a.code = b.code
           WHERE NOT EXISTS (SELECT 1
                             FROM table_c c
                             WHERE a.code = c.code)',
  @params = null, 
  @browse_information_mode = 2

This returns source_table values table_a and table_b but not table_c.

I need the list of accessed tables.

Any ideas on how I would achieve this?

Makes sense - since that system stored procedure describes what makes up the **result set** - and no column from `table_c` is included in the result set .... — marc_s, Dec 25 '20 at 13:36
Does this answer your question? [List of tables used in an SQL Query](https://stackoverflow.com/questions/16692344/list-of-tables-used-in-an-sql-query) — Hasan Fathi, Dec 25 '20 at 13:59
If you are able to capture xml query plans (via trace) then the xml has each table which is used in query. Take a look at using set showplan_xml on in a query window then look at the results of executing a query. If you save the resulting file as xml you should easily be able to find the tables accessed. — Steve Ford, Dec 26 '20 at 01:58

Steve Ford · Accepted Answer · 2020-12-26T18:04:19.800

How about creating a temporary view, then use the built in dependency functions to get the referenced tables and then drop the views, rinse and repeat, replace the @sql with your own statements:

declare @tsql varchar(max) = 'SELECT 
               a.code, b.customer_name
           FROM table_a a 
           INNER JOIN table_b b ON a.code = b.code
           WHERE NOT EXISTS (SELECT 1
                             FROM table_c c
                             WHERE a.code = c.code)'

exec ('create view vwtmp as ' + @tsql)

select OBJECT_NAME(referencing_id) referencing_entity,
        o.[type_desc] as referenced_entity_type, referenced_entity_name
from sys.sql_expression_dependencies d
inner join sys.objects o
    on d.referenced_id = o.[object_id]
where OBJECT_NAME(referencing_id) = 'vwtmp'

exec ('drop view vwtmp')

This is what my test returns:

referencing_entity  referenced_entity_type  referenced_entity_name
------------------  ----------------------  ----------------------
vwtmp               USER_TABLE              table_a
vwtmp               USER_TABLE              table_b
vwtmp               USER_TABLE              table_c

SQL Server: How get a list of tables accessed by a SELECT statement

1 Answers1