Is it possible to show columns really used in a view

Question

Consider a view like this:

CREATE TABLE test.t_test
(
    a int, 
    b int, 
    c int, 
    d int, 
    e int
)

CREATE VIEW test.v_test AS 
    WITH w_test AS  
    (
        SELECT *, c + d AS cd_computed 
        FROM test.t_test
    )
    SELECT a AS a_renamed, c, cd_computed 
    FROM w_test

If I do (1)

SELECT * 
FROM information_schema.view_column_usage 
WHERE view_name = 'v_test'

I will obtain a,b,c,d,e

If I do (2)

SELECT c.name 
FROM sys.columns c
INNER JOIN sys.views v ON c.object_id = v.object_id
WHERE v.name = 'v_test'

I will obtain a_renamed,c,cd_computed

But I would like to obtain only original columns that are really use by the "planner" to do the job, so, only: a,c,d the other columns b and e are in fact useless in these view and should not be used by the "planner".

I think/hope than planner can rewrite the view before execute AS

WITH w_test AS 
(
    SELECT a, c, c + d AS cd_computed -- rewrite of this SELECT with only the usefull columns
    FROM test.t_test
)
SELECT a AS a_renamed, c, cd_computed 
FROM w_test

Is there any way to retrieve this information?

Answer 1

You can use the sys.dm_sql_referenced_entities system function for this.

select
  r.referenced_server_name,
  r.referenced_database_name,
  r.referenced_schema_name,
  r.referenced_entity_name,
  r.referenced_minor_name
from sys.dm_sql_referenced_entities ('dbo.v_test', 'OBJECT') r
where r.referenced_minor_name is not null;

db<>fiddle

Note that sys.sql_expression_dependencies does not work in your case because unfortunately you are not specifying the schema name in the table references (a bad practice in itself).

---

If you only want the columns that actually end up being selected, you could parse the query plan using XQuery.

with xmlnamespaces (
    default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
select
  x.colRef.value('@Database', 'sysname'),
  x.colRef.value('@Schema', 'sysname'),
  x.colRef.value('@Table', 'sysname'),
  x.colRef.value('@Column', 'sysname')
from @queryplan.nodes('//TableScan/DefinedValues/DefinedValue/ColumnReference') x(colRef)

union all
  
select
  x.colRef.value('@Database', 'sysname'),
  x.colRef.value('@Schema', 'sysname'),
  x.colRef.value('@Table', 'sysname'),
  x.colRef.value('@Column', 'sysname')
from @queryplan.nodes('//IndexScan/DefinedValues/DefinedValue/ColumnReference') x(colRef);