Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 10 Aug 2007 @ 18:45:54 GMT


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


Subj:   Re: Bteq SHOW TABLE export
 
From:   Curley, David

Dave,

This bteq script will generate separate files for a variety of object types in one or more databases. You could combine it with Robert Hanson's unix script to limit to a single table. This would become the infile text for that exec bteq <<BTQEND call.

     .os rm runme.btq
     .export file=runme.btq
     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 ()  -- could be tablename =
     $1 using Robert Hanson's method
       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 ()  -- could be tablename =
     $1 using Robert Hanson's method
       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 ()  -- could be tablename =
     $1 using Robert Hanson's method
       and tablekind in ('T','Q','P','N','I','M','G','V')
     ) x
     order by tablekind, tablename, stp;
     .export reset
     .run file=runme.btq
     .quit
>br>

I've not included it, but it's run from a unix script that tacks on the logon string and cleans up afterward. (It uses the generated files to check any DDL changes into an RCS repository. So in addition to our regular change control, we have a parallel version that represents day- over-day changes that are actually present in production.)


Dave Curley



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019