Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Find the number of rows of all tables in a
 
From:   JAMES PARK

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>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016