-- Step 2 run this query after replacing the MYDBXXXX with your database of interest.
SELECT '; INSERT INTO TBLRECCNT SELECT ' || '''' || TRIM(TABLENAME) || '''' || ', COUNT(*) FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || '' FROM DBC.TABLES WHERE TABLEKIND = 'T' AND DATABASENAME = 'MYDBXXXX' ;
-- Step 3. Copy paste the output of the above query (Remember to discard the column headers if you copied that one) and run the query (same session from where you created the volatile table).
-- step 4 get all the details
SELECT * FROM TBLRECCNT;
P.S. This strategy works if your user id has SELECT access to all of the tables in the DB. Another caveat could be a lock by the load utilities on any of the tables in the DB. in that case you will have to remove the select count(*) statement for that table from STEP 3.
There was another way to do from Teradata Admin too, I just don't recall all the steps now though, pretty similar, save there was no need for volatile tables.
I figured out mistake- we have to many databases . I ran your query succefully however the tables names are trimmed in step2 . For example: while querying databse xxx, table customer, the query generated is like below:Note - CUSTO instead of Customer. I tried tweakign the query but was unsuccessful. Do you know whats the problems is?
INSERT INTO TBLRECCNT SELECT 'CUSTOMER', COUNT(*) FROM XXX.CUSTO ;
I tried various combinations of the query you sent . As i was learnign our datawarehouse more i understood some of the information i provided were wrong ( Frankly, this is first time i am using a teradata and I was trying to get it work the same way as an Oracle datawarehouse).
We have about 45 databases ! one for each clients . and we have many table for each clients . I modified the query you had given to generate the SQL for all of them and saved inthe temporary table . It had really worked for me .