Question
Mark Runyan · Apr 9, 2021

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

Product version: Caché 2017.1
$ZV: 2017.2.2
00
2 0 4 97
Log in or sign up to continue

Replies

Attention, I am now advertising on my own behalf ;-)
You can do that very easily with Caché Monitor


Caché Monitor connect via TCP to Caché\IRIS (like the ODBC driver).

Andreas

Andreas,  I'll take a look at Caché Monitor.

See CREATE FUNCTION

  • Create your own counting function:
    CREATE FUNCTION my.GetCalcTableExtentSize(IN SchemaName SYSNAMEIN TableName SYSNAMERETURNS BIGINT(''PROCEDURE LANGUAGE OBJECTSCRIPT
    {
     quit ##class(%SQL.Manager.Catalog).GetCalcTableExtentSize(SchemaNameTableName)
    }
  • Now you can use it in queries:
    select table_schema "Schema"table_name TableName,my.GetCalcTableExtentSize(table_schema,table_nameRowCount from information_schema.tables where table_type in ('BASE TABLE','VIEW')

Thanks Vitaliy, this looks like an elegant solution, however I lack permissions to create functions in our hosted database.  I'll have to see if I can test the procedure else where and submit a ticket to have it created.  Cheers, - Mark