Archives of the TeradataForum

Message Posted: Fri, 20 May 2011 @ 10:16:24 GMT

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

You can get the no. of rows in all the tables in database from the below steps...

1. Create a volatile table

     Create    Volatile Table lidlbjh.Table_Row_Count
     (
     Tablename VarChar(30)
     ,RowCount Integer
     )
     On Commit Preserve Rows;

2. Run the below dynamic sql to get the insert commands

     Select    'Insert into lidlbjh.Table_Row_Count
     Select ' || '''' || trim(databasename) || '.' || trim(tablename) || ''',' ||
     ' Count(*) From ' || trim(databasename) || '.' ||
     trim (tablename) || ';' (Title '')
     From dbc.tables
     Where tablekind =  't'
     and databasename = 'hci'
     order by tablename;

3. Run the insert statement generated by above dynamic sql.

4. Now run the below select statement.

     Select tablename
     , rowcount (varchar(20))(format 'x(20)')
      from table_row_count
     order by 3 desc;

Thanks,

Mahesh

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