New post
Question
· Apr 9, 2021

Measure all the row counts of every table in a Cache database

#Databases #SQL #Caché

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
Discussion (4)1
Log in or sign up to continue
Vitaliy Serdtsev · Apr 12, 2021

See CREATE FUNCTION

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