Archives of the TeradataForum

Message Posted: Tue, 02 Aug 2005 @ 16:37:41 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi All,

I'm involved in a project where I need to be able to match the view column name with it's associated base table column name, including those views where a join is occurring between two tables with common base column names. Here's an example using two views against a common set of base tables.

View DDL:

     Create View DBSV1.View1
     (Cust_Acct, Cust_Name, Txn_Ttl)
     As Locking DBS1.Table1 for Access Locking DBS2.Table2 for Access
     Select a.customer_account, a.customer_name, b.customer_txn_ttl
     From DBS1.Table1 A Join DBS2.Table2 B
     On A.customer_account = b.customer_account
     ;

View DDL:

     Create View DBSV1.View2
     (Cst_Acc, Cst_Nm)
     As Locking DBS1.Table1 for Access
     Select a.customer_account, a.customer_name
     From DBS1.Table1
     ;

Ultimately, what I want to end up with is a list of columns on base tables, and every occurrence where that base table column is accessed in a view (whether under the same name or a different name), with the associated column names from the views, like:

     DBS1.Table1.customer_account is read by DBSV1.View1.Cust_Acct
     DBS1.Table1.customer_account is read by DBSv1.View2.Cst_Acc
     DBS1.Table1.customer_name is read by DBSV1.View1.Cust_Name
     DBS1.Table1.customer_name is read by DBSV1.View2.Cst_Nm
     DBS1.Table2.customer_txn_ttl is read by DBSv1.View1.Txn_Ttl

What I'm trying to determine are the base table column names that are not accessed by users, regardless of the seemingly endless number of views which may read a specific column, and this is a precursor step needed to tie the view column names in queries back to the source base table column names to see where columns are and aren't accessed.

I started under the impression that column ID's were unique, but I'm now given to they are unique within the database and tablename. During my trials, I would get miss-matches because the same column ID would be on Table1 and Table2, but be non-related columns (Customer_Account on one table, and Activity_Date on the second table, but with the same column ID in their associated base tables).

Does anyone have an example of SQL that has successfully developed these results, or any suggestions as to approach for acquiring these results successfully? Any information or tidbits would be most welcome.

Thanks,

John

	<Prev	Next>		<<First	<Prev	Next>	Last>>