New post
Question
· Feb 1, 2018

Cache I need system tables that store number of rows.

#Caché

Hi 

I am new to cache and accessing it using odbc (other data sources) from excel. I need to know how to get hold of the number of rows for each every object in my current schema. 

I can find table name using following query but I need number of rows to estimate size of each table.

SELECT *
FROM %Dictionary.ClassDefinition ClassDefinition
Discussion (2)0
Log in or sign up to continue
Dmitry Maslennikov · Feb 1, 2018

Instead of your query, I would recommend using query from INFORMATION_SCHEMA.Tables table

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  CLASSNAME
FROM INFORMATION_SCHEMA.Tables

In Cache we don't have a table with the number of rows in each table, you have to measure it by yourself for each table. But you can write SQL Function which will count it for you, and you can use this function in the query above.

SELECT count(*) FROM Sample.Person
0 0
Eduard Lebedyuk · Feb 1, 2018

You can use this SQL directly:

SELECT COUNT(1)
FROM Your.Table

Or if you want to pass class name as an argument, you can wrap it in SQL procedure:

Class Utils.Dictionary
{

/// Call from SQL: SELECT Utils.Dictionary_GetExtentSize('Utils.Persistent') As ExtentSize
/// write ##class(Utils.Dictionary).GetExtentSize("Utils.Persistent")
ClassMethod GetExtentSize(class) As %Integer [ SqlProc ]
{
  /// Convert class name to table name.
  /// You can skip this step if you have table name already
  #define ClassSQLTable(%c) ($$$comClassKeyGet(%c,$$$cCLASSsqlschemaname)_"."_$$$comClassKeyGet(%c,$$$cCLASSsqltablename))
  set table = $$$ClassSQLTable(class)

  /// Quoter2 is called to escape table name if required
  set table = ##class(%CSP.UI.Portal.SQL.Home).Quoter2(table)

  /// Execute dynamic SQL
  /// Really %sqlcq.<NAMESPACE>.cls<NUMBER>
  #dim rs As %SQL.ISelectResult
  set rs = ##class(%SQL.Statement).%ExecDirect(,"SELECT COUNT(1) AS extentSize FROM " _ table)

  /// Get first result
  do rs.%Next()
  set extentSize = rs.extentSize

  quit extentSize
}

}

And call like this:

SELECT Utils.Dictionary_GetExtentSize('Utils.Persistent') As ExtentSize
0 0