Exporting Requesttest

Database
Enthusiast

Exporting Requesttest

Hi all,

Is there any way to export the request text from dbc.tables based on database.tablename using BTEQ?

My requirement is to export all the View definition of the request text based on viewname in dbc.tables...

please note that view have lot of sub views and tables inside that .i want to export all the definitions of subviews and tables too which is belonging to particular view.
4 REPLIES
Enthusiast

Re: Exporting Requesttest

Why don't you export the output of

SHOW SELECT * FROM MYDB.MYVIEW;

that way all the views/tables/JIs will be pulled out as well.
Enthusiast

Re: Exporting Requesttest

Hi Joedsilva,
thanks for your suggestion. obviously
SHOW SELECT * FROM MYDB.MYVIEW ;will give result when the viewname is hardcoded . but i want to export the result of more than 2000+ views . is there any way to be done using BTEQ without hardcoding the viewname?
Enthusiast

Re: Exporting Requesttest

You can generate the "show" statements itself at run time and use it to extract the view definitions ...

i.e. something like this.

** Generate all the show statements.
.export file = showstmts.sql;

SELECT 'SHOW SELECt * FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';' (TITLE '')
FROM DBC.TABLES
WHERE DATABASENAME = 'MYVWDB'
ORDER BY DATABASENAME , TABLENAME
;

.export reset;

** now extract all the DDLs.
.export file = ddls.sql;
.run file = showstmts.sql;
.export reset;

The flaw would be ofcourse that if two views refer to the same table/view you would find two copies of DDLs in the output file.
Enthusiast

Re: Exporting Requesttest

Hi Joedsilva

it works.. thanks!!