Query to return table sizes
I've been accessing Cache tables from a developer/reporting side, but am now involved in a project to create a data warehouse for our application. I'm trying to find a query I can use to return the sizes of all the tables in the database, so we can identify the largest tables and handle those individually. Can someone give me a query I can run against our Cache database to return the sizes of all the tables from largest to smallest?
Thanks for the help
Product version: Caché 2017.1
By size, do you mean number of rows or size on disk?
Size on Disk. We have some tables with narratives. While only a single row of data, the text can be pretty large. Thanks
Will the size of the global (with data,index,stream) suit you? If yes, then see this.
Sure, I can try it and see. I am only interested in the table size on disk however (don't need index information or anything else). Thanks
Ok, the index can be omitted.
The table data is stored in different globals: *D - for data, *I - for indexes, *S - for streams. You can get the size of the data parts you want.
Example [Size()]:
Here's a full example relating global size back to related classes/tables, expanding on @Vitaliy.Serdtsev's example:
Note that if you use inheritance with persistent classes sharing an extent this'll be a little bit off (since the same global is used for multiple classes and will be double counted).
Note, this also assumes default global names - if you have customized these, you'll want to just get all globals. Also, replace 'C:\InterSystems\IRIS20\mgr\user' with the appropriate database directory for your instance.
Calculating detailed class/table size.
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue