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?

Answer 1

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