Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 23 Jun 2011 @ 18:10:54 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Find the number of rows of all tables in a

Esteemed users,

Teradata on Windows.

I've assisted a developer to extract the number of rows of all tables in a database. As a SYSDBA I have no problem following the steps below. But the developer has gotten an error "conversion from type 'DBNULL' to type 'String' is not valid" while inserting.

She has Create, Delete table, Insert, Update and Delete on all tables on the database.

The steps that she and I follow are below.

Thank you.

Run these using SQL Assistant. 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 SYSDBA.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 XXXXX.Table_Row_Count
     Select ' || '''' || trim(databasename) || '.' || trim(tablename) || ''',' || ' Count(*)
     From ' || trim(databasename) || '.' || trim (tablename) || ';' (Title '')
     From dbc.tables
     Where tablekind =  't'
     and databasename = 'XXXXXX'
     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 1 desc;

  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023