Get Column Alias from View AND Phisical Attribute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get Column Alias from View AND Phisical Attribute

Author

Topic

akuhnle
Starting Member

1 Post

Posted - 2008-05-27 : 18:37:25

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources