Get fields used within SQL query

Question

I would like to be able to return a list of all fields (ideally with the table details) used by an given SQL query. E.g. the input of the query:

SELECT t1.field1, field3
FROM dbo.table1 AS t1
INNER JOIN dbo.table2 as t2
    ON t2.field2 = t1.field2
WHERE t2.field1 = 'someValue'

would return

+--------+-----------+--------+
| schema | tablename | field  |
+--------+-----------+--------+
| dbo    | table1    | field1 |
| dbo    | table1    | field2 |
| dbo    | table1    | field3 |
| dbo    | table2    | field1 |
| dbo    | table2    | field2 |
+--------+-----------+--------+

Really it needs to make use of the SQL kernal (is that the right word? engine?) as there is no way that the reader can know that field3 is in table1 not table2. For this reason I would assume that the solution would be an SQL. Bonus points if it can handle SELECT * too!

I have attempted a python solution using sqlparse (https://sqlparse.readthedocs.io/en/latest/), but was having trouble with the more complex SQL queries involving temporary tables, subqueries and CTEs. Also handling of aliases was very difficult (particularly if the query used the same alias in multiple places). Obviously it could not handle cases like field3 above which had no table identifier. Nor can it handle SELECT *.

I was hoping there might be a more elgant solution within SQL Server Management Studio or even some function within SQL Server itself. We have SQL Prompt from Redgate, which must have some understand within its intellisense, of the architecture and SQL query it is formatting.

UPDATE: As requested: the reason I'm trying to do this is to work out which Users can execute which SSRS Reports within our organisation. This is entirely dependent on them having GRANT SELECT permissions assigned to their Roles on all fields used by all datasets (in our case SQL queries) in a given report. I have already managed to report on which Users have GRANT SELECT on which fields according to their Roles. I now want to extend that to which reports those permissions allow them to run.

Redgate effectively wrote their own intellisense with SQL Prompt, and such things aren't simple; it's a full application that integrates with SSMS (and ADS in Preview). You're effectively asking "How do I reverse engineer T-SQL?" or "Can someone write me an application to reverse engine a set of T-SQL batches". That's *far* too broad a question. At best, you would ned to write your own application to do this, and when you get stuck, post the code you have, explain why it's not working, and tell us your expected results. — Thom A, Feb 26 '20 at 16:40
Why do you need this kind of information, especially formatted like this? What is the purpose? Maybe if you share the problem you need to solve we might be able to provide an answer. Redgate's SQL Prompt is using data from the system information schema views (INFORMATION_SCHEMA) and other system tables. — Thailo, Feb 26 '20 at 16:48

score 1 · Answer 1 · answered Feb 26 '20 at 16:48

The column table names may be tricky because column names can be ambiguous or even derived. However, you can get the column names, sequence and type from virtually any query or stored procedure.

Example

Select column_ordinal
      ,name
      ,system_type_name 
  From sys.dm_exec_describe_first_result_set('Select * from YourTable',null,null )

score 1 · Answer 2 · answered Feb 28 '20 at 15:29

I think I have now found an answer. Please note: I currently do not have permissions to execute these functions so I have not yet tested it - I will update the answer when I've had a chance to test it. Thanks for the answer goes to @milivojeviCH. The answer is copied from here: https://stackoverflow.com/a/19852614/6709902

The ultimate goal of selecting all the columns used in an SQL Server's execution plan solved:

USE AdventureWorksDW2012

DBCC FREEPROCCACHE

SELECT dC.Gender, dc.HouseOwnerFlag, 
SUM(fIS.SalesAmount) AS SalesAmount 
FROM 
    dbo.DimCustomer dC INNER JOIN
    dbo.FactInternetSales fIS ON fIS.CustomerKey = dC.CustomerKey 
GROUP BY dC.Gender, dc.HouseOwnerFlag
ORDER BY dC.Gender, dc.HouseOwnerFlag
/*
query_hash          query_plan_hash
0x752B3F80E2DB426A  0xA15453A5C2D43765
*/

DECLARE @MyQ AS XML;

-- SELECT qstats.query_hash, query_plan_hash, qplan.query_plan AS [Query Plan],qtext.text 
SELECT @MyQ = qplan.query_plan 
 FROM sys.dm_exec_query_stats AS qstats 
  CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan 
  cross apply sys.dm_exec_sql_text(qstats.plan_handle) as qtext 
  where text like '% fIS %'
and query_plan_hash = 0xA15453A5C2D43765

SeLeCt @MyQ

;WITH xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DISTINCT
    [Database] = x.value('(@Database)[1]', 'varchar(128)'),
    [Schema]   = x.value('(@Schema)[1]',   'varchar(128)'),
    [Table]    = x.value('(@Table)[1]',    'varchar(128)'),
    [Alias]    = x.value('(@Alias)[1]',    'varchar(128)'),
    [Column]   = x.value('(@Column)[1]',   'varchar(128)')
FROM   @MyQ.nodes('//ColumnReference') x1(x)

Leads to the following output:

Database                  Schema Table            Alias Column
------------------------- ------ ---------------- ----- ----------------
NULL                      NULL   NULL             NULL  Expr1004
[AdventureWorksDW2012]    [dbo]  [DimCustomer]    [dC]  CustomerKey
[AdventureWorksDW2012]    [dbo]  [DimCustomer]    [dC]  Gender
[AdventureWorksDW2012]    [dbo]  [DimCustomer]    [dC]  HouseOwnerFlag
[AdventureWorksDW2012]    [dbo]  [FactInternetSal [fIS] CustomerKey
[AdventureWorksDW2012]    [dbo]  [FactInternetSal [fIS] SalesAmount

Get fields used within SQL query

2 Answers2