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

• lpfy

  Old Hand

  Points: 305

  More actions

  August 17, 2012 at 12:54 am

  #266192

  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

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  August 17, 2012 at 1:02 am

  #1526921

  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.

  ---

• lpfy

  Old Hand

  Points: 305

  More actions

  August 17, 2012 at 1:47 am

  #1526939

  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

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  August 17, 2012 at 2:12 am

  #1526948

  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

  ---