Get Column Alias From View AND Phisical Attribute

Hello Everyone,

I am creating a Metadata management application for a business intelligence platform on SQL Server 2005.

For this purpose, I've set out to consolidate all DB-object metadata (Databases, Tables, Columns, Views) into a single repository that business users can browse through. It has been fairly straightforward so far, but I've hit a wall in the views department.

In fairly simple terms, I want to know which physical columns are selected in a view, with the twist of also knowing the columns' alias. This seems to be impossible as far as I can tell, it is easy enough to get both of these independently, but I can't figure out how to connect an alias to an actual column.

(I prefer to use the metadata catalog views to the INFORMATION_SCHEMA as I need to know the object_id's)

Aliases are easy enough to obtain:

select object_id, column_id, name
from sys.columns
where object_id = object_id('SomeViewsName');


As are the 'physical' columns:
SELECT
t.object_id as TABLE_OID,
c.column_id as COLUMN_OID,
c.name
FROM
sys.views v
JOIN sys.sql_dependencies d
ON d.object_id = v.object_id
JOIN .sys.objects t
ON t.object_id = d.referenced_major_id
JOIN sys.columns c
ON c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
WHERE
d.class < 2 AND
v.name = 'SomeViewsName';


As I've said before, the problem is joining these two datasets. One of the problems associated with this is that the latter query also returns columns used in JOIN statements, even if they are not projected in the select part of the view definition.

Ultimately I'd like to get this result:

Alias used in View, physical table's object id, physical column's id

Am I missing something?

Thank you very much in advance for your help.