how to get Alias name in columns for views

Question

Post reply

how to get Alias name in columns for views

Amrita bhatia

Right there with Babe

Points: 718
More actions
September 20, 2005 at 6:34 am

#165989

hi
we are looking for mapping between views and table that make the views
so if we take example of nortwind database in sql
and create view as
create view v1 as
SELECT dbo.Orders.OrderID AS [new orderid], dbo.[Order Details].ProductID AS [new productid]
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
as in above case we have used alias for orderid as "new orderid" and productid as "new productid".
my question is there a way we can get the data as
TABLE_NAME COLUMN_NAME COLUMN_ALIAS
orders orderid [new orderid]
[order details] ProductID [new productid]
i have tried sp_depends on v1 and querying information_schema views but am not able to get mapping between column name and [new orderid]
so we need a query to find which column of view depends on which column on which table
thanks
Amrita

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This is what you need :

Select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where View_Name = 'vwFormsUsage'

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 2

hi

thanks for reply

the above query will give orderid and productid but its not telling that orderid is mapped as "new orderId" in alias

there is no mapping between columns in table and view, which we require

amrita

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 3

hi

thanks for reply

the above query will give orderid and productid but its not telling that orderid is mapped as "new orderId" in alias

there is no mapping between columns in table and view, which we require

amrita

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

remi - I think the requirement is not just to get the columns used but the alias used against each...there must be a way to get this information - nothing I can think of off the top of my head...

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

can't really find anything except to "eyeball" it using:

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vwFormsUsage'

..doesn't help I know..well, if anyone can find it remi can so we'll just wait for the RGR'us to get back...

**ASCII stupid question, get a stupid ANSI !!!**

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Hmm I wonder... Only thing I can think of is to search the viewdefinition for '%myAlias%' string patterns, then you're on your own.

I think it can be pretty challenging to programmatically find what's an alias and what's not, especially since you can alias a column i three different ways (six ways if you remove the quotes)

select someColumn as 'myNewColumn',

someColumn 'myNewColumn',

'myNewColumn' = someColumn

from myTable

..all different ways of aliasing.

I have no idea actually, but I don't think that the alias is stored anywhere as metadata...

/Kenneth

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

You forgot ", subqueries/derived table and other views... Still working on it but it doesn't look good.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

And I forgot all the converts, functions....

That's impossible to do reliably without some text parsing... no wonder it's not included in the system views. The columns_usage doesn't reffer only to the select part, it also contains the joins/order by, where... I also assume that such info is kept on derived tables and such and such. I guess you're out of luck on this one.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

Heh, I didn't even bother to go into nested aliases

select x.foobar as 'fuubar'

from

( select foo as 'foobar' from bar ) x

etc etc...

/Kenneth

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

I'm sure something could be done with this given TIME which I unfortunately do not have....

The name in syscolumns actually stores the "alias" not the original column name...so..

select * from syscolumns where id = object_id('vwMyView') gives you the alias names...can't figure out how to trace this to the "root" columns...

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Tried that... there's no way to trace the original colid of the base table to the colid of the columns in the view under a new name. This is the missing key to make a relation of any kind. There's always the xtype of the column that could serve but that doesn't filter a lot. And as I said you also have to take care of the join, where... parts too. It's doable but I'm not taking a few days to make an sql parser or learning how they work to make this, sorry.

noeld SSC Guru Points: 96590 More actions · Answer 12

The Problem with this question is that is trying the INVERSE Approach. Only using Modeling tools you can get that information but then again you are pusching the Model to the DB not the Other way around and if you used the Model then you would hav never need to look for this in the First Place

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Nice approach, how would you implement that in an automated manner?

... Or am I missing the point completly?

noeld SSC Guru Points: 96590 More actions · Answer 14

Modeling tools give you that option!! You specify your schema in the Model, all the definitions and all the constraints then all necessary information is already there.

If you need to create an Oracle DB just tell the tool to push (create SQL Statements) that to an Oracle server and you are done or if yo needed it to be on ( our favorite ) SQL Server just do it but then again the metadata is handeled on the MODEL not on the Server Platform it is implemented

* Noel