Archives of the TeradataForum
Message Posted: Tue, 02 Aug 2005 @ 16:37:41 GMT
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.
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 ;
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|