SQL Server (2005+) query to return the base table and base column (field) for each column (field) in a view

Question

I want a query that will return a row for each column in a view, and a row for the view itself.

There should be a column basetable in the result that gives the base table for the column in the current row, and a column basefield in the result that gives the name of the column in the underlying query (for renamed columns). It would be a bonus if any calculations could also be included in the basefield column.

I don't think this can be done. Am I wrong?

In the example below "what goes here" should be replaced by table1 or table2 as appropriate in the basetable column, and a, b, or c as appropriate in the basefield column.

create table table1 (a int, b int)
create table table2 (a int, c int)
go
create view view1 as select table1.a, table1.b, table2.c from table1 left join table2 on table1.a = table2.a
go

select * from 
(
 select 'View' objecttype,O.name viewname,'' fieldname,0 column_id,'' typename,'' max_length,'' [precision], '' scale, '' is_identity,
    'what goes here' basetable, '' basefield
 from sys.objects O where O.type='V' and O.[schema_id] = 1

 union all

  select 'Field' objecttype,object_name(C.[object_id]) viewname,C.name fieldname,C.column_id,T.name typename,C.max_length,C.precision,C.scale,C.is_identity,
  'what goes here' basetable, 'what goes here' basefield
  from sys.columns C
 left join sys.types T on C.user_type_id=T.system_type_id
 where C.[object_id] in (select O.[object_id] from sys.objects O where O.type='V')
) I
where viewname in ('view1')
order by viewname, column_id

drop view view1
drop table table1
drop table table2

Answer 1

You know it might not map 1 to 1? A column in a view might be the result of several (or even none!) columns from different tables.

That said, it should be possible by parsing the source for the view. But the sql code would be procedural/imperative in nature and not at all trivial.