How to extract all tables (including where references) within a SQL query?

Question

I'm working on a huge database, and I have to get a list of all tables used for 100+ procedures. So far I've been able to get a query that returns all the tables that the procedure is getting data from, but no all the tables that are referenced. For example:

WITH src AS 
(
  SELECT [procedure] = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name),
      ref = 
      COALESCE(QUOTENAME(d.referenced_server_name)   + N'.', N'')
    + QUOTENAME(d.referenced_schema_name) + N'.'
    + QUOTENAME(d.referenced_entity_name),
                d.referenced_database_name
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o
  ON d.referencing_id = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE o.[type] = N'P'
)
SELECT [procedure],ref, referenced_database_name
FROM src
GROUP BY [procedure],ref,referenced_database_name;

Using that query if the procedure looks something like this:

Select * from A where date=(select max(date) from B)

I would only get table A, but I also need table B to show up. It seems that all the SQL queries around only gather data that is actually being integrated to the final select statements, but references like "where", "in", "not in", coalesce, etc, are left aside. Maybe It's kind of raw, but I just wondering how can I do to make sure that if a table is merely mentioned in any way I can get it. I haven't been able to find anything, also tried with an python SQL parser, but with no results whatsoever.

Don't you need to worry about queries in code, scripts, spreadsheets, etc. as well as internal stored procedures, triggers, constraints, indexed expressions? — David G. Pickett, Aug 18 '20 at 21:07
No, so far as I'm concerned just the databases tables (there many databases with their own schemas being used all around) used/mentioned on the stored procedures — Fernando Sagasta, Aug 18 '20 at 21:24

score 1 · Accepted Answer · answered Aug 18 '20 at 21:27

here's a more modern way:

SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d 
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
ORDER BY proc_name, table_name

a prettier approach:

SELECT DISTINCT 
      [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.type_desc
FROM sys.dm_sql_referenced_entities ('dbo.usp_test1', 'OBJECT') d
JOIN sys.objects o ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('U', 'V')

Technically sys.objects is deprecated, so in the future YMMV.

score 0 · Answer 2 · answered Apr 04 '23 at 17:16

I ran both queries from the OP and the other answers, but neither provided all tables. The OP's original query provided 12,454 tables within one database and John's first query provided 2,921 tables. There was some overlap, and using the revised query below provided all 13,255 tables.

SELECT DISTINCT *
FROM
(
    SELECT
        S.name + '.' + O.name AS 'Procedure',
        D.referenced_database_name AS 'Database',
        D.referenced_schema_name AS 'Schema',
        D.referenced_entity_name AS 'Table'
    FROM sys.sql_expression_dependencies D
    JOIN sys.objects O ON O.object_id = D.referencing_id
    JOIN sys.schemas S ON S.schema_id = O.schema_id
    WHERE O.type = N'P'

    UNION ALL

    SELECT DISTINCT
        SCHEMA_NAME(P.SCHEMA_ID) + '.' + P.name AS 'Procedure',
        DB_Name() AS 'Database',
        SCHEMA_NAME(T.SCHEMA_ID) AS 'Schema',
        T.name AS 'Table'
    FROM sys.sql_dependencies D
    JOIN sys.procedures P ON P.object_id = D.object_id
    JOIN sys.tables T ON T.object_id = D.referenced_major_id
) T

How to extract all tables (including where references) within a SQL query?

2 Answers2