Archives of the TeradataForum

Message Posted: Mon, 13 May 2002 @ 12:16:40 GMT

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

Here's a bteq script you can alter and use to populate a table with databasename, tablename, and row count for whatever database(s) you need. I have mine scheduled in cron.

Thanks,

Scott

/***********************************************/
.logon username,password;
/* remove sql file from last time  */
.os rm /home/ssummers/table_counts.sql;
.set width  250;

/*     This table can already created or you could drop and recreate each
time the script is run   */

/*
        CREATE SET TABLE ssummers.table_counts ,NO FALLBACK ,
             NO BEFORE JOURNAL,
             NO AFTER JOURNAL
             (
              databasename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
              tablename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
              row_count DECIMAL(10,0))
        PRIMARY INDEX ( databasename ,tablename );

*/

/* clean out table before starting if you don't drop and recreate */

delete from  ssummers.table_counts;

/* begin exporting dynamic sql to be run later */
.export file=/home/ssummers/table_counts.sql;

select 'insert into ssummers.table_counts select  '||
''''||trim(a.databasename)||''''|| ',
'||''''||trim(a.tablename)||''''||',count(*) as table_count from '||
trim(a.databasename)||'.'||trim(a.tablename)||';'
from dbc.tables a
where tablekind='t'
/*  constrain by databasename or tablename if you like */
and databasename = 'ssummers';

/* stop exporting  */
.export reset;

/* Now run the sql generated above  */
.run file=/home/ssummers/table_counts.sql skip=2;

.logoff;

/***************************************/

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