Matching a View's columns to it's underlying table's columns - 11-05-2003 , 12:41 PM
Using SQL Server 2000, I'm trying to put together a query that will
tell me the following information about a view:
The View Name
The names of the View's columns
The names of the source tables used in the view
The names of the columns that are used from the source tables
Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
below, which gives me the View Name, Source Table Name, and Source
Column Name. And I can easily enough get the View columns from the
syscolumns table. The problem is that I haven't figured out how to
link a source column name to a view column name. Any help would be
v_obj.name as ViewName,
t_obj.name as SourceTable,
t_col.name as SourceColumn
v_obj.xtype = 'V'
and dep.id = v_obj.id
and dep.depid = t_obj.id
and t_obj.id = t_col.id
and dep.depnumber = t_col.colid
Re: Matching a View's columns to it's underlying table's columns - 11-06-2003 , 02:41 AM
garyderousse (AT) yahoo (DOT) com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428 (AT) posting (DOT) google.com>...
records that the dependency exists, but not exactly what the
dependency is. The mapping of view to table columns could be 1:N or
M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
worth the effort to try and capture the detailed column mapping.
Re: Matching a View's columns to it's underlying table's columns - 11-06-2003 , 04:44 PM
Thanks for the information, even though it wasn't what I wanted to hear.
sql (AT) hayes (DOT) ch (Simon Hayes) wrote in message news:<60cd0137.0311060041.35542cec (AT) posting (DOT) google.com>...