How to get list of table used in any particular query in SQL Server?

Question

I need help in getting list of all table used in any particular query in SQL Server whether its part of join or any subquery.

I am expecting output as below.

Lets say query is-> "select A.*, B.col1, B.col2 from table1 A inner join table2 B on A.abc=b.abc"

Expected output is list of table used in above query. which is table1, table2

Is there any way i can get the tables used for any random query?

-----------------------------------------------------------

Thankyou Everyone - I get it worked for SQL Server. I am looking for same thing for Netezza also.

Is there any equivalent for sys.dm_exec_describe_first_result_set in Netezza which can provide same results?

Not propery a list, but with query plan you can see how the query was built with Sql Server engine — Joe Taras, Oct 30 '19 at 15:58
If you can wrap the query into an SP or view (for example) and compile it, you can check the dependencies in `sys.sql_dependencies`. Won't work with dynamic SQL of course. — EzLo, Oct 30 '19 at 15:59
Partial answer https://stackoverflow.com/a/45737413/5070879 only when columns is returned as resultset. — Lukasz Szozda, Oct 30 '19 at 15:59
There are just too many ways to get this wrong. If your code uses views, functions or dynamic sql you are going to miss tables. Not to mention aliases. — Sean Lange, Oct 30 '19 at 16:17

Stefan Steiger · Answer 1 · 2019-10-31T18:26:09.103

You can put your query into a stored-procedure (e.g. sp_Dummy), then you can get the dependencies with

SELECT DISTINCT 
     o.name 
    ,o.type_desc 
FROM sys.dm_sql_referenced_entities(QUOTENAME(N'dbo') + N'.' +  QUOTENAME(N'sp_Dummy'), 'OBJECT') AS dm 

INNER JOIN sys.objects AS o 
    ON o.object_id = dm.referenced_id 

INNER JOIN sys.schemas AS sch 
    ON sch.schema_id = o.schema_id

Here a procedure shell

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Dummy]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_Dummy] AS' 
END
GO


-- =======================================================
-- Author:      XXXX
-- Create date: 31.10.2019
-- Description: Dummy procedure to infer dependencies
-- =======================================================
ALTER PROCEDURE [dbo].[sp_Dummy]
     @in_object_type AS varchar(2) 
    ,@in_object_uid AS uniqueidentifier 
    ,@in_report_date AS datetime2 
    -- your query's parameters go here...
AS
BEGIN
    SET NOCOUNT ON;

    -- your query goes here
END


GO

This won't get dynamic sql, though - but I think that's as good as it can get.

You can extract all paramters with:

System.Text.RegularExpressions.Regex.Matches(sql, @"\@\w+")
    .Cast<Match>().Select(m => m.Value).ToList();

How to get list of table used in any particular query in SQL Server?

-----------------------------------------------------------

1 Answers1