How to find base tables/views for views

Database
SAP
N/A

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 !!!

7 REPLIES

Re: How to find base tables/views for views

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';

SAP
N/A

Re: How to find base tables/views for views

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

Re: How to find base tables/views for views

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

SAP
N/A

Re: How to find base tables/views for views

Nope :( please help me !!!

Re: How to find base tables/views for views

You can contact your TD poc in your org :)

Re: How to find base tables/views for views

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.

Teradata Employee

Re: How to find base tables/views for views

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.