Archives of the TeradataForum

Message Posted: Sun, 04 Feb 2007 @ 14:38:35 GMT

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

I use a bteq script like this:

     .set width 5000;
     .SET TITLEDASHES OFF;

     .run file = "bteqlogon.txt"

     select date, time;

     .os rename "show_all_10.txt" "show_all_11.txt"
     .os rename "show_all_09.txt" "show_all_10.txt"
     .os rename "show_all_08.txt" "show_all_09.txt"
     .os rename "show_all_07.txt" "show_all_08.txt"
     .os rename "show_all_06.txt" "show_all_07.txt"
     .os rename "show_all_05.txt" "show_all_06.txt"
     .os rename "show_all_04.txt" "show_all_05.txt"
     .os rename "show_all_03.txt" "show_all_04.txt"
     .os rename "show_all_02.txt" "show_all_03.txt"
     .os rename "show_all_01.txt" "show_all_02.txt"
     .os rename "show_all.txt" "show_all_01.txt"
     .os del "show_all.txt"

     select date, time;

     .export report file = "show_all.txt"

     .run file = "02_gen_show_all_sql.TXT"

     .export reset

     select date, time;

     .os rename "DDL_backup_10.txt" "DDL_backup_11.txt"
     .os rename "DDL_backup_09.txt" "DDL_backup_10.txt"
     .os rename "DDL_backup_08.txt" "DDL_backup_09.txt"
     .os rename "DDL_backup_07.txt" "DDL_backup_08.txt"
     .os rename "DDL_backup_06.txt" "DDL_backup_07.txt"
     .os rename "DDL_backup_05.txt" "DDL_backup_06.txt"
     .os rename "DDL_backup_04.txt" "DDL_backup_05.txt"
     .os rename "DDL_backup_03.txt" "DDL_backup_04.txt"
     .os rename "DDL_backup_02.txt" "DDL_backup_03.txt"
     .os rename "DDL_backup_01.txt" "DDL_backup_02.txt"
     .os rename "DDL_backup.txt" "DDL_backup_01.txt"
     .os del "DDL_backup.txt"

     select date, time;

     .export report file = "DDL_backup.txt"

     .run file = "show_all.TXT"

     .export reset

     select date, time;

     .quit;

This is the sql to generate the "show" statements:

     select case when tablekind = 'i' then 'show join index '
                 when tablekind = 'j' then 'show journal '
                 when tablekind = 'g' then 'show trigger '
                 when tablekind = 'p' then 'show procedure '
                 when tablekind = 'm' then 'show macro '
                 when tablekind = 't' then 'show table '
                 when tablekind = 'v' then 'show view '

            end || trim(databasename) || '.' || trim(tablename) || ' ;' (title '')
       from dbc.tables  dt
      where dt.tablekind in('i','j','g','p','m','t','v')
        AND DATABASENAME IN
            (
             'databasename1','databasename2',...
            )
        and trim(tablename) not like all ('al%','et%','lt%','tl%','uv')
        and dt.tablename not in
            ( select trim(childtable) || '_' || trim(indexid)
                 from dbc.all_ri_children
            )
      group by 1
      order by 1;

Don Stubbs
Teradata Certified Master

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