Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Statistics about table statistics
From:   Geoffrey Rommel

  I would like to be about to produce a report that lists all statistics on all columns with date collected and be able to maintain this info in a table (s) for historical trends.  

  Does anyone have an easy way to do this? I would rather not develop a VB script to strip out the information from a series of HELP STATISTICS...  

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

     #--- 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;
     |") or die "Could not run bteq: $!";

      my @tbls = ();
      while () {
         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;
     |") 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


  <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: 28 Jun 2020