Archives of the TeradataForum

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

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

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

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