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

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.

Answer

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.