Deconstructing a view

[denimined]

(This is a re-submission in hopes of getting an answer. )

I need a way to deconstruct a view. That is, for a given column in a view, find the reference table / column that was used to construct the view. I know that

Select * from information_schema.view_column_usage

will give me the source tables and columns for the entire view, but it does not give me the specific reference for any given column. Any suggestions?

(MS SQL 2000)

Thanks.

 

[donutman]

The table sysobjects will give you the id of any object including views. From that you can get the colid (relative position) of each column of the view by using the syscolumns table. A join of information_schema.view_column_usage with sysobjects and syscolumns should give you everything you want, right?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[denimined]

Almost correct. The
information_schema.view_column_usage
does not give a reference to column's position in the view. Nor does it give you a reference to any alias that the column may have. From the syscolumns side, the aliased column has no reference point to what table / column composes that field.

Any other suggestions?