To find row counts of all tables within a DB

------------------------------------------------------------------------------------
--
-- tbl-rowcount.sql
--
-- find the tables within the specified database and get a rowcount
-- run section (1) along 
-- then copy the answer set from section (1) to section (2)
-- then run sections (2) and (3) together
-- finally print the answer set containing the rowcount values
-----------------------------------------------------------------------------------
--(1)
Drop table lidlbjh.Table_Row_Count;
Create    Volatile Table lidlbjh.Table_Row_Count
(
Tablename VarChar(30)
,RowCount Integer
)
On Commit Preserve Rows;

Select    'Insert into lidlbjh.Table_Row_Count Select ' || '''' || trim(databasename) || '.' || trim(tablename) || ''',' || ' Count(*) From ' || trim(databasename) || '.' ||
trim(tablename) || ';' (Title '')
From dbc.tables
Where tablekind =  't'
and databasename = 'hci'
--and ( tablename like 'v_rals%' or tablename like 'whs%' or tablename like 'ms%' or tablename like 'retro%')
order by tablename;

--(2)
/*------------ copy answer set here and then execute ----------------------------------------------------*/
  
Delete from lidlbjh.Table_Row_Count all; 
 
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_ALLPROV_CONTEXT', Count(*) From HCI.T_ALLPROV_CONTEXT;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_ALLPROV_NOCONTEXT', Count(*) From HCI.T_ALLPROV_NOCONTEXT;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_BU_NORMS', Count(*) From HCI.T_BU_NORMS;


--(3)
select current_date as "_As_of_Date_"
  ,tablename
  , rowcount (varchar(20))(format 'x(20)')
 from table_row_count
order by 3 desc;