Measure all the row counts of every table in a Cache database
Hi, in the end I want a report like this:
| Schema | TableName | RowCount |
| SYSTEM | RADplus_event_log | 18,251,308 |
| DocM | log_image_transfer | 7,196,272 |
| SYSTEM | RADplus_audit_database_tables | 3,345,757 |
| … | ||
| SYSTEM | view_summary_mvmt | 0 |
In my case I have about 1,230 tables/views in the database. The initial approached I've used is to use this SQL to generate SQL to make the actual measures:
SELECT
string('select ''', table_schema ,''' as "Schema", ''', table_name,''' as TableName, COUNT(*) as RowCount from ' , table_schema,'.',table_name , ' UNION ALL ') as TR FROM information_schema.tables
This generates SQL code like this:
select 'CPO' as "Schema", 'view_version' as TableName, COUNT(*) as RowCount from CPO.view_version UNION ALL
select 'CWSDocM' as "Schema", 'category_definition' as TableName, COUNT(*) as RowCount from CWSDocM.category_definition UNION ALL
select 'CWSDocM' as "Schema", 'document_archive_log' as TableName, COUNT(*) as RowCount from CWSDocM.document_archive_log UNION ALL
It works pretty good, but Cache appears to only support up to 128 commands strung together by UNION ALL. So I had to break up my SQL file into 10 separate files in which I executed and appended the results. Job done, but I'd like a more facile way to do this. My Cache Db is hosted by a third party, so I don't have many ways to interface with it other than an ODBC interface to WinSQL. Thanks for any ideas!
- Mark