Post reply

how to get Alias name in columns for views

  • 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

  • September 20, 2005 at 6:56 am

    #591281

    This is what you need :

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

  • September 20, 2005 at 7:35 am

    #591313

    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

  • September 20, 2005 at 7:35 am

    #591312

    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

  • September 20, 2005 at 7:36 am

    #591315

    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 !!!**

  • September 20, 2005 at 7:53 am

    #591321

    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 !!!**

  • September 20, 2005 at 8:15 am

    #591347

    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

  • September 20, 2005 at 8:21 am

    #591351

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

  • September 20, 2005 at 9:02 am

    #591367

    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.

  • September 20, 2005 at 9:29 am

    #591382

    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

  • September 20, 2005 at 9:34 am

    #591386

    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 !!!**

  • September 20, 2005 at 9:42 am

    #591392

    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.

  • September 20, 2005 at 2:01 pm

    #591485

    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

  • September 20, 2005 at 2:03 pm

    #591486

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

    ... Or am I missing the point completly?

  • September 20, 2005 at 2:12 pm

    #591491

    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

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

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