Archives of the TeradataForum

Message Posted: Fri, 10 May 2002 @ 20:04:43 GMT

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

I use the following SQL to write the SQL to count all the rows in a set of tables and write them to a 'statistics' table that feeds our metadata: The DDL I used to create the statistics table follows the SQL. You would need to change the WHERE statement at the bottom of the query. I run this query which generates SQL which I then copy/paste into queryman and run the whole set. I have similar code to generate unique value counts and row counts per value as well as some application specific statistics.

/*
STATISTIC TYPE = TABLE_ROW_CT
DESCRIPTION
THIS STATISTIC DESCRIBES HOW MANY ROWS ARE IN A TABLE
*/
sel
(
'insert into development.X_statistics'||
'(                                         '||
'     Statistic_Dt                       '||
',    Statistic_Tm                       '||
',    Statistic_Type_Ds                  '||
',    Database_Nm                        '||
',    Table_Nm                           '||
',    Column_Nm                          '||
',    Column_Value_Ds                    '||
',    Statistic_Ct                       '||
')                                         '||
'sel  DATE                               '||
',    TIME                               '||
',    ''TABLE_ROW_CT''                   '||
','                               ||
''''||trim(upper(databasename))||''''       ||
','                               ||
''''||trim(upper(tablename))||''''          ||
',    NULL                               '||
',    NULL                               '||
',    count(*)                           '||
' from '                          ||
trim(upper(databasename))               ||
'.'                               ||
trim(upper(tablename))                  ||
' group by  1,2,3,4,5,6,7                  '||
';                                         '
) as XQL
from  dbc.tables
where databasename='NAME'
and   tablekind='T'
group by 1
;

CREATE SET TABLE development.X_statistics ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      Statistic_Dt DATE FORMAT 'yyyy-mm-dd',
      Statistic_Tm FLOAT,
      Statistic_Type_Ds VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
      Database_Nm VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
      Table_Nm VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
      Column_Nm VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
      Column_Value_Ds VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      Statistic_Ct INTEGER)
PRIMARY INDEX ( Statistic_Tm );

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