Written by

Database Administrator/Report Writer at Youth East Side Services

Question Mark Runyan · Apr 9, 2021

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

#Caché #Databases #SQL

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)0

Add reply

Comments

Andreas Schneider · Apr 10, 2021

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

0 0

Mark Runyan Apr 12, 2021 to Andreas Schneider

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

0 0

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

Mark Runyan Apr 12, 2021 to Vitaliy Serdtsev

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

0 0