Archives of the TeradataForum

Message Posted: Fri, 14 Dec 2007 @ 19:48:41 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Using BTEQ isn't a problem, you just write SQL that generates SQL, send the results to an export file (or files), and run it. Whether BTEQ is SysExecSQL is appropriate is up to you and the context in which you're running.

Here's an example that creates separate DDL scripts for objects in the database.

     .os rm runme.btq
     .export file=runme.btq  /* this is the file we're about to create and
     run */
     select x.txt (title '') from (
     select 1 stp, tablekind, tablename, '.export file=' ||
     trim(databasename) || '.' || trim(tablename) || '.ddl' txt
     from dbc.Tables t
     where databasename in (/* list of databases to monitor */)
        and tablekind in ('T','Q','P','N','I','M','G','V')
     union
     select 2 stp, tablekind, tablename, 'show ' ||
                 case
                   when tablekind in ('T','Q') then 'table '
                   when tablekind in ('P') then 'procedure '
                   when tablekind in ('N') then 'index '
                   when tablekind in ('I') then 'join index '
                   when tablekind in ('M') then 'macro '
                   when tablekind in ('G') then 'trigger '
                   when tablekind in ('V') then 'view '
                 end
                  || trim(databasename) || '.' || tablename ||
     ';'
     from dbc.Tables t
     where databasename in (/* list of databases to monitor */)
        and tablekind in ('T','Q','P','N','I','M','G','V')
     union
     select 3 stp, tablekind, tablename, '.export reset'
     from dbc.Tables t
     where databasename in (/* list of databases to monitor */)
        and tablekind in ('T','Q','P','N','I','M','G','V')
     ) x
     order by tablekind, tablename, stp;
     .export reset
     .run file=runme.btq  /* now run the file we just created */
     .quit

The file runme.btq looks like

     .export file=database1.tablename1.ddl
     Show table database1.tablename1;
     .export reset
     .export file=database2.tablename2.ddl
     Show view database2.tablename2;
     .export reset.
     export file=database3.tablename3.ddl
     Show trigger database3.tablename3;
     .export reset

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>