How to list the source table name of columns in a VIEW (SQL Server 2005)

  • Just wondering how to list column name and table name in one query for a view

    for example

    a view named as viewC, create by tbl1 inner join tbl2, contain a,b,c,d, 4columns.

    a,b from tbl1, c,d from tbl2

    How to Select COLUMN_NAME, DATA_TYPE, column_default, character_maximum_length, sourceTableNAME FROM information_schema.columns where table_name='viewC' together?

    the result I want is:

    column_NAME, table_name

    a,tbl1

    b,tbl1

    c,tbl2

    d,tbl2

    Thanks

  • Have a look in INFORMATION_SCHEMA.VIEW_COLUMN_USAGE for the view-related info - you may have to join that to another i_s view to get everything you want.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks,

    I have wrote my query and it works

    SELECT INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,

    INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,

    INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH

    FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE INNER JOIN

    INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.VIEW_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND

    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME

  • Nice work - thanks for posting back.

    May I suggest that you consider using table aliases - it will make your query much more readable. Eg

    select t.col1, t.col2 from tablewithaverylongnameindeedohyes t

    --rather than

    select tablewithaverylongnameindeedohyes.col1, tablewithaverylongnameindeedohyes.col2 from tablewithaverylongnameindeedohyes

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 4 posts - 1 through 3 (of 3 total)

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