Archives of the TeradataForum

Message Posted: Thu, 28 Jun 2001 @ 20:47:08 GMT

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

VB? Heresy!

I don't know about easy, but I have written a Perl script that does more or less what you describe. I'm just printing the results to stdout, but you could put them in a table instead.

     #!/usr/bin/perl -w
     # Teradata: Show all stats in a database.  These are just reported,
     # not put in a table or anything.

     $db = shift;

     @tbls = tbls_in_db($db);

     foreach $tbl (@tbls) {
        show_stats("$db.$tbl");
     }
     exit;

     #--- SUBROUTINES
     #
     #--- Return all tables in a database.
     sub tbls_in_db {
      my $db = shift;

      open(BTEQ, "bteq <&1
     .run file=/home/grommel/dba/Top /* logon info */
      select TableName
      from DBC.Tables
       where TableKind = 'T'
         and DatabaseName = '$db'
       order by 1;
     .quit
     ZZ
     |") or die "Could not run bteq: $!";

      my @tbls = ();
      while () {
         chomp;
         next if /^TableName/;
         next unless /^(\w+)\s*$/;
         push @tbls, $1;
      }
      close BTEQ;

      return @tbls;
     }

     #--- Run HELP STATISTICS on a table; display results.
     sub show_stats {
     my $tbl = shift;

     open(BTEQ, "bteq <&1
     .run file=/home/grommel/dba/.guest
     .set width 250
      help statistics $tbl;
     .quit
     ZZ
     |") or die "Unable to run BTEQ";

     print "Table $tbl\n";
     while () {
     # If no stats were found, quit now.
        if (/no statistics/) {
           close BTEQ;  return;
        }
        next unless /^[\d\/]{8,10}\s/;
        print "  $_";  ## Or put in a table, or put in an array and sort
     later.
     }

     return;
     }

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