Hello,

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
appreciated.

Gary


select
v_obj.name as ViewName,
t_obj.name as SourceTable,
t_col.name as SourceColumn
from
sysobjects t_obj,
sysobjects v_obj,
sysdepends dep,
syscolumns t_col
where
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
order by
v_obj.name,
t_obj.name,
t_col.name

garyderousse (AT) yahoo (DOT) com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428 (AT) posting (DOT) google.com>...
I don't believe that this information is available - sysdepends
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.

Simon

Simon,

Thanks for the information, even though it wasn't what I wanted to hear.


Gary


sql (AT) hayes (DOT) ch (Simon Hayes) wrote in message news:<60cd0137.0311060041.35542cec (AT) posting (DOT) google.com>...