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 .
You can refer to an expert's solution:
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';
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.
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.