Archives of the TeradataForum
Message Posted: Thu, 23 Jun 2011 @ 18:10:54 GMT
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.
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;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|