How to find relationship between view columns and table columns?

• Rachel Collingridge

  SSC Enthusiast

  Points: 112

  More actions

  November 20, 2004 at 6:10 pm

  #59735

  Hi all,

  I'm using the INFORMATION_SCHEMA views to query remote databases.  I use INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION to return the T-SQL definition of each view, and INFORMATION_SCHEMA.VIEW_COLUMN_USAGE to return the underlying tables and columns that a view utilises.

  So, INFORMATION_SCHEMA.VIEW_COLUMN_USAGE provides me with a relationship between a view and columns used, but I would like to know the relationship between a view column and column(s) used.

  If ViewX.ColumnX returns a composite of TableA.ColumnA and TableA.ColumnB and ViewX.ColumnY returns TableA.ColumnC, then:

  Using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE will tell me that ViewX uses TableA.ColumnA, TableA.ColumnB and TableA.ColumnC, but doesn't tell me in which of ViewX's columns they're used.

  I sort of think that this is impossible to do without parsing the T-SQL stored against INFORMATION_SCHEMA.VIEW_DEFINITION which is a daft idea.  I want to tell the user that they can't have this relationship exposed, but want to confirm that it can't be done.  It doesn't look like the SQL-DMO View object exposes this relationship either.

• November 23, 2004 at 8:00 am

  This was removed by the editor as SPAM

• Ste_P

  SSCarpal Tunnel

  Points: 4430

  More actions

  November 25, 2004 at 3:25 am

  #531512

  Hi Rachel,

  Sorry its been a while since you posted....

  Came across this whilst looking for something completely different and just thought of your post.

  http://www.sqlservercentral.com/scripts/contributions/611.asp

  Hope it helps.

  Have fun

  Steve

  We need men who can dream of things that never were.