Archives of the TeradataForum

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

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

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>>