Ho to retrieve column aliases for a view ?

  • Hi all.

    What I am trying to get is the alias assigned to a field when creating a view.

    I know I can look at the SQL text of the view definition in information_schema.views but I don't want to parse the view definition so I tryed with other system views and tables.

    I see that [font="Courier New"]information_schema.view_column_usage [/font] tells me al the columns and the tables involved in the view, but I could not find where the alias is stored.

    Look at this example:

    create table test_table( id_test int null)

    go

    create view test_view as

    select test_table.id_test as new_name

    from test_table

    go

    select * from information_schema.view_column_usage c where view_name = 'test_view'

    go

    The result is:

    [font="Courier New"]VIEW_CATALOG|VIEW_SCHEMA|VIEW_NAME|TABLE_CATALOG|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME

    TEST_DB|dbo|test_view|TEST_DB|dbo|test_table|id_test[/font]

    What I would like to get is something similar to:

    [font="Courier New"]VIEW_NAME|TABLE_NAME|COLUMN_NAME|ALIAS_NAME

    test_view|test_table|id_test|new_name[/font]

    Is it possible to get this information ?

    Thanks in advance

    Giacomo

  • hi,

    try this

    select *,

    (select name from syscolumns

    where id in (select id from sysobjects

    where name = View_name)) ALIAS_NAME

    from information_schema.view_column_usage

    where view_name = 'test_view'

    ARUN SAS

  • arun.sas (5/20/2009)


    select *,

    (select name from syscolumns

    where id in (select id from sysobjects

    where name = View_name)) ALIAS_NAME

    from information_schema.view_column_usage

    where view_name = 'test_view'

    Unfortunately this does not work if the view has more than one field (the select on syscolumns returns more than one row).

    Anyway your suggestion put me on the right track: In order to return one single record, I need to filter on column_id. This is not available in view_column_usage view, I need to look in other sys.* tables

    Thanks

    Giacomo

  • What I am trying to get is the alias assigned to a field when creating a view.

    You can get this information from by joining sys.views to sys.columns

    What I would like to get is something similar to:

    [font="Courier New"]VIEW_NAME|TABLE_NAME|COLUMN_NAME|ALIAS_NAME

    test_view|test_table|id_test|new_name[/font]

    Is it possible to get this information ?

    Aside from a table column, a view column can also be an expression, function, or constant so there is no correlation between a view column and a table column. I think your best bet will be to parse VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS

  • Charles Hearn (5/22/2009)


    Aside from a table column, a view column can also be an expression, function, or constant so there is no correlation between a view column and a table column.

    In my case I am sure this does not happen, but you are right: in the most general case there's no guarantee that an alias can be connected to a table column. maybe that is the reason why the association view alias/table column is not soter anywhere in sistem tables...

    I think your best bet will be to parse VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS

    That is exactly was I was trying to avoid... 🙁

    I start thinking there is no way to do it just querying system tables and views...

    Thanks anyway!

    Giacomo

  • it took a while for this one to soak in. I read the requirement a couple of times, and kept thinking it's gotta be possible by using the stored proc sp_depends, but that proc does not return the alis , just the actual dependancy.

    if you sp_help sp_depends from the master database, you can see it is doing a series of joins to get the information...all i did was grab the key SELECT statemetn, join it one more time to syscolumns, and *poof*, the actual dependacy is available witht eh table and column name it depends on....

    see if this is the information you are looking for; replace "vw_blah" with your view name, or parameterize it to make it easier.

    CREATE TABLE [dbo].[BLAH] (

    [BLAHID] INT NULL,

    [BLAHTEXT] VARCHAR(30) NULL)

    GO

    CREATE VIEW VW_BLAH

    AS

    SELECT

    BLAHID AS THEID,

    BLAHTEXT AS THETEXT,

    BLAHID + 100 AS SOMECLACULATION,

    getdate() as THEDATE

    FROM BLAH

    GO

    select

    'name' = (s6.name+ '.' + o1.name),

    type = substring(v2.name, 5, 16),

    updated = substring(u4.name, 1, 7),

    selected = substring(w5.name, 1, 8),

    'column' = col_name(d3.depid, d3.depnumber) ,

    'tblalias' =object_name(sc.id),

    'colalias' = sc.name

    from sys.objects o1

    ,master.dbo.spt_values v2

    ,sysdepends d3

    ,master.dbo.spt_values u4

    ,master.dbo.spt_values w5 --11667

    ,sys.schemas s6

    ,syscolumns sc

    where o1.object_id = d3.depid

    and o1.type = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'

    and u4.type = 'B' and u4.number = d3.resultobj

    and w5.type = 'B' and w5.number = d3.readobj|d3.selall

    and d3.id = object_id('vw_blah')

    and o1.schema_id = s6.schema_id

    and deptype < 2

    and sc.id = d3.id

    and sc.colid = d3.depnumber

    --results

    name type updated selected column tblalias colalias

    --------- ---------------- ------- -------- ---------- ---------- ----------

    dbo.BLAH user table no yes blahID VW_BLAH THEID

    dbo.BLAH user table no yes BLAHTEXT VW_BLAH THETEXT

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Wow! The last part I was missing was using [font="Courier New"]master.dbo.spt_values[/font] ! 🙂

    I tested with other views in my DBs and it seems to work fine!

    Thank you for your precious help!:-D

    Regards

    Giacomo

  • Sorry to write again about this topic... 🙁

    It seems to work but it doesn't work correctly: your solution outputs the list of the aliases but they are not correctly associated with the column:

    If I define test_view like this:

    [font="Courier New"]CREATE TABLE [dbo].[test_table](

    [id_test] [int] NULL,

    [f1] [int] NULL,

    [f2] [int] NULL

    )

    ALTER view [dbo].[test_view] as

    select f2, test_table.id_test as new_name

    from test_table[/font]

    If I execute your query this is the output:

    [font="Courier New"]name | type | updated | selected | column | tblalias | colalias

    dbo.test_table | user table | no | yes | id_test | test_view | f2[/font]

    And this is the correct result:

    [font="Courier New"]name | type | updated | selected | column | tblalias | colalias

    dbo.test_table | user table | no | yes | f2 | test_view | f2

    dbo.test_table | user table | no | yes | id_test | test_view | new_name[/font]

    Problem seems that it just outputs the column in the same order of the aliases of the view, but there there is not a correct relationship: if you swap the order of the aliases the output is not correct.

    Anyway, I am working following your suggestion and investigating in that direction...

    Giacomo

  • Hi Guys,

    I got the similar requirement but I am struggling to complete it

    if you have found the solutions can you please share with me

    Thanks in advance

    Regards,

    ~Sri

  • srinath.chai (3/17/2010)


    I got the similar requirement but I am struggling to complete it

    post it it new thread/topic and elaborate your problem

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • srinath.chai (3/17/2010)


    Hi Guys,

    I got the similar requirement but I am struggling to complete it

    if you have found the solutions can you please share with me

    Thanks in advance

    Regards,

    ~Sri

    the devil is in the details...what are you doing that is not answered in this thread? what is differnet?

    give us specific details.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 11 posts - 1 through 11 (of 11 total)

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