------------------------------------------------------------------------------------
--
-- 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;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_CAD', Count(*) From HCI.T_CAD;
Insert into lidlbjh.Table_Row_Count Select 'HCI.t_cad_backup', Count(*) From HCI.t_cad_backup;
Insert into lidlbjh.Table_Row_Count Select 'HCI.t_cad_load', Count(*) From HCI.t_cad_load;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_DBUSERS', Count(*) From HCI.T_DBUSERS;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_DXCODE_CONVERSION', Count(*) From HCI.T_DXCODE_CONVERSION;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_ECS_ADDR', Count(*) From HCI.T_ECS_ADDR;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_ECS_MEM', Count(*) From HCI.T_ECS_MEM;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_FAC', Count(*) From HCI.T_FAC;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_FACILITY_CONTEXT', Count(*) From HCI.T_FACILITY_CONTEXT;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_FACILITY_NOCONTEXT', Count(*) From HCI.T_FACILITY_NOCONTEXT;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_MERGENM', Count(*) From HCI.T_MERGENM;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_MVA', Count(*) From HCI.T_MVA;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_MVH', Count(*) From HCI.T_MVH;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_MVNA', Count(*) From HCI.T_MVNA;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_PA_ZIPCODE_AND_REP', Count(*) From HCI.T_PA_ZIPCODE_AND_REP;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_PHARMACY_ADD', Count(*) From HCI.T_PHARMACY_ADD;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_PHARMACY_FIX', Count(*) From HCI.T_PHARMACY_FIX;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_PROF', Count(*) From HCI.T_PROF;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_PROF_CONTEXT', Count(*) From HCI.T_PROF_CONTEXT;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_PROF_NOCONTEXT', Count(*) From HCI.T_PROF_NOCONTEXT;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_REDBOOK_FIX', Count(*) From HCI.T_REDBOOK_FIX;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_SMIS_CUSTINFO', Count(*) From HCI.T_SMIS_CUSTINFO;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_SMIS_GRPINFO', Count(*) From HCI.T_SMIS_GRPINFO;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_TAMED_GRPNUM', Count(*) From HCI.T_TAMED_GRPNUM;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_TAMED_INFO', Count(*) From HCI.T_TAMED_INFO;
Insert into lidlbjh.Table_Row_Count Select 'HCI.T_UPMC', Count(*) From HCI.T_UPMC;


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