How can I get a list of referenced tables from a query?

This is more of a hack, but you can use references support to figure things out for more complex queries.

The main idea is that sql server keeps track of references for procedures, so we create a dummy procedure which contains your code, and let sql server return the dependencies for it:

CREATE TABLE #tqueries (
    query nvarchar(max))
INSERT INTO #tqueries
SELECT  'insert into NonExistingTable(i) select  from another.[table]'
UNION ALL
SELECT  ' (select count() from Sometable)'
UNION ALL
SELECT  'if 1 = 0 begin 
    create table t (i int)
    insert into t
    values(@@spid)
    insert into t2
    values(@@spid)
    end'
DECLARE @crx CURSOR
SET @crx = CURSOR READONLY FORWARDONLY STATIC FOR
SELECT  query
,   'sp' + replace(cast(newid() AS nvarchar(100)), '-', '')
FROM    #tqueries
OPEN @Crx
WHILE 1 = 1
BEGIN
    DECLARE @query nvarchar(max), @sp sysname
    ,   @sql nvarchar(max)
    FETCH NEXT FROM @Crx INTO @query, @sp
    IF @@FETCHSTATUS  0
        BREAK
    -- Create a dummy procedure which refers to things...
    SET @sql = 'create or alter procedure ' + @sp + '
    as
    begin
        ' + @query + N'
    end'
    BEGIN TRAN
        EXEC    (@sql)
        -- See references to all objects...
        SELECT  referencedentityname, *
        FROM    sys.sqlexpressiondependencies se
        WHERE   se.referencingid = objectid(@sp)
    ROLLBACK TRAN
END

I populated #tqueries with some sample queries. The cursor creates a procedure in a transaction, and then sys.sqlexpressiondependencies will contain the procedure dependencies. When transaction rolls back, the procedure will be gone.

You can store the generated dependencies in a table variable which won't be rollbacked as part of transaction rollback.

There are some limitations of course:

• Code incompatible with stored procedures, ie. create view and other multi-batch stuff, RETURN, USE Database
• Dynamic sql where tables aren't known at parse time

In my experience also, when someone wants to know tables that are referenced by code, it means you don't control or test your code properly and the real problem is that, rather than something else.

-- Sample stored queries table
CREATE TABLE #storedqueries (
    id INT PRIMARY KEY,
    querytext NVARCHAR(MAX)
);
INSERT INTO #storedqueries VALUES
(1, 'SELECT * FROM dbo.Customers c JOIN dbo.Orders o ON c.CustomerID = o.CustomerID'),
(2, 'UPDATE [Sales].Invoices SET Amount = 100 WHERE InvoiceID = 1'),
(3, 'DELETE FROM Products WHERE ProductID = 5'),
(4, 'SELECT * FROM [Users] WHERE IsActive = 1');
-- Get all user tables in current DB (optionally filter by schema if needed)
SELECT
    s.name AS schemaname,
    t.name AS tablename
INTO #tables
FROM sys.tables t
JOIN sys.schemas s ON t.schemaid = s.schemaid;
select * from #tables
;WITH MatchedTables AS (
    SELECT
        q.querytext,
        -- fully qualified table name with schema (optional)
        t.tablename AS qualifiedtablename,
        t.tablename
    FROM #storedqueries q
    JOIN #tables t ON
        -- Match on bracketed full name, e.g. [dbo].[Customers]
        q.querytext LIKE '%\[' + t.tablename + '\]%' ESCAPE '\'
        OR
        -- Match on unbracketed preceeded by dot (.)
        q.querytext LIKE '%' + '.' + t.tablename + '%'
        OR
        -- Match on just table name with spaces around
        q.querytext LIKE '% ' + t.tablename + ' %'
)
-- Aggregate all matched table names per query
SELECT
    querytext,
    STRINGAGG(qualifiedtablename, ', ') WITHIN GROUP (ORDER BY qualifiedtablename) AS referencedtables
FROM MatchedTables
GROUP BY querytext
-- Cleanup temp tables (optional)
DROP TABLE #storedqueries;
DROP TABLE #tables;

Assuming that your queries reference existing tables - I will first create two sample tables. These will be recorded as a row (for each table object) in sys.objects.

--      S a m p l e    D a t a :
Create Table tbla (id Int, namea Text, datea Date);
Insert Into tbla
  VALUES(1, 'A''s 1st row', '2025-07-20'), 
        (2, 'A''s 2nd row', '2025-07-23'), 
        (3, 'A''s 3rd row', '2025-07-25');
Create Table tblb (id Int, tblaid Int, nameb Text, dateb Date);
Insert Into tblb
  VALUES(1, 1,  'B''s 1st row for aid is 1', '2025-07-20'), 
        (2, 1, 'B''s 2nd row for aid is 1', '2025-07-21'),
        (3, 3, 'B''s 1st row for aid is 3', '2025-07-21');

CREATE OR ALTER VIEW v1 AS 
Select a.id, a.namea, a.datea, b.nameb, b.dateb
From   tbla a
Left Join tblb b ON(b.tblaid = a.id);

This will be reflected as a row in sys.objects, but also a row per each referenced object in sys.sqlexpressiondependencies. With the code bellow you can get what you asked for.
You can LOOP it droping the views after getting the result.

SELECT
    o.typedesc AS referencingdescription, 
    OBJECTNAME(referencingid) AS referencingentityname,
    o2.typedesc AS referenceddescription,
    referencedentityname
FROM sys.sqlexpressiondependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencingid = o.objectid
INNER JOIN sys.objects AS o2 ON sed.referencedid = o2.objectid
WHERE referencingid = OBJECTID(N'v1');