How to transform select query

Question

I have DDL:

drop table names
drop table salary

create table names(
id int, 
name1 varchar(50),
char1 varchar(50),
char2 varchar(50))

insert into names values (1,'name1','char1','chara'),
                         (2,'name2','char2','charb'),
                         (3,'name3','char3','charc'),
                         (4,'name4','char4','chard'),
                         (5,'name5','char5','charf');

create table salary(
id int, 
salary int,
bonus int,
oldsalary int)

insert into salary values (1,500,245,354),
                         (2,600,345,246),
                         (3,60,365,334),
                         (4,55,545,364),
                         (5,25,345,374);

And have the many SQL query' s (one of them)

    SELECT n.name1,
       s.salary,
       s.bonus,
       ( s.bonus + s.salary ) AS Sumsalary
FROM   names n
       INNER JOIN salary s
               ON n.id = s.id

I want to write select query that retrieves all fieldnames from tables that used in SQL query divided by usage in this select query with some additional info mentioned in screenshot.

Format of data that I want retrieve from this query:

Answer 1

You could get the list of tables & columns for queries if you have access to the system views:

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ns)
SELECT DISTINCT st.TEXT AS QueryText, 
                C.value('./@Table', 'varchar(50)') As Tab,
                C.value('./@Column', 'varchar(50)') As Col
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY query_plan.nodes('//ns:ColumnReference') as T(C)
WHERE cp.ObjType = 'Adhoc' AND
      St.Text Like '%s.bonus%' AND
      st.Text Not Like '%WITH XMLNAMESPACES%' AND 
      C.value('./@Table', 'varchar(50)') IS NOT NULL

This gives the following results:

QueryText            Tab       Col
SELECT n.name1, ...  [Names]   id
SELECT n.name1, ...  [Names]   name1
SELECT n.name1, ...  [Salary]  bonus
SELECT n.name1, ...  [Salary]  id
SELECT n.name1, ...  [Salary]  salary

You could then use these results as a means of linking to sys.columns to determine which coulmns are missing etc.