All Forums Database
SAP 73 posts Joined 08/14
30 Sep 2014
How to find base tables/views for views

Hi Team,
I have a list of 300 views . I need to find the base table/views of 300 views . Please suggest me a better way to do it .
Thanks !!!

SAP
Raja_KT 1246 posts Joined 07/09
30 Sep 2014

You can refer to an expert's solution:
http://developer.teradata.com/blog/ulrich/2011/11/extract-and-analyse-database-object-dependencies
 
You can also think of exporting the result of  this to  file and then do a .run file of the  result.
select 'Show view' ||Trim(Databasename)||'.'||Trim(Tablename)||';' (title '')
from DBC.TABLES where databasename='your_db' 
and tablekind = 'V';

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
30 Sep 2014

thanks Raja. But y dont teredata system doesnt have such informations handy ? :(

SAP

Raja_KT 1246 posts Joined 07/09
30 Sep 2014

Teradata MDS , you can view. Do you have MDS?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
30 Sep 2014

Nope :( please help me !!!
 

SAP

Raja_KT 1246 posts Joined 07/09
30 Sep 2014

You can contact your TD poc in your org :)

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

andydoorey 35 posts Joined 05/09
01 Oct 2014

You can use a similar query as above to generate:
show select * from viewname;
statements and run those.  This will give the DDL for each object used in the query.
 
Another option if you have dbql switched on for objects is to run a select from each of the views and then look in dbqlobjtbl to see which objects have been used.  This can be a simpler way of doing things if you just want a list of object and database names.
 

fnewbrough 17 posts Joined 05/11
01 Oct 2014

You can also use a recursive query to parse the view ddl. I used to have a piece of SQL I wrote to do this which worked fairly well but I've misplaced it or I'd share.

You must sign in to leave a comment.