...
The availability and accuracy of table statistics depends on how often you collect and store the statistics on the database server. These details are only for when you set enableRowCount="approximate":
| Database | Prerequisite | How Value is Obtained | See Also |
|---|
| Oracle | Run DBMS_STATS.GATHER_TABLE_STATS to collect or update the statistics. | Permissions Requires DBA privilege. Source Value in dba_tables.num_rows for each table. Sample Query | Code Block |
|---|
SELECT CONCAT(OWNER,CONCAT('.', TABLE_NAME)) as TABLE_NAME, NUM_ROWS FROM DBA_TABLES WHERE OWNER IN ('&USER_NAME'); |
| Oracle Database Tips |
| SQL Server | None. Statistics are kept automatically. | Permissions Source Uses a sum of row_counts from sys.dm_db_partition_stats to get the total number of rows in the heap or clustered index on the table. Sample Query | Code Block |
|---|
SELECT (OBJECT_SCHEMA_NAME(object_id)+ '.' + OBJECT_NAME(object_id)) as TABLE_NAME,
CAST(row_count AS float) as NUM_ROWS FROM sys.dm_db_partition_stats
where OBJECT_SCHEMA_NAME(object_id) IN ('DAT28'); |
| MSDN Blog |
| DB2 | Run RUNSTATS to collect or update the statistics. | Permissions The following roles have the necessary permission: - ACCESSCTRL
- DATAACCESS
- DBADM
- SECADM
- SQLADM
- SYSADM
- SYSCTRL
- SYSMAINT
- SYSMON
Source Uses syscat.tables view. Sample Query | Code Block |
|---|
SELECT CONCAT(TRIM(TABSCHEMA), CONCAT('.', TABNAME)) as TABLE_NAME, SUM(CARD) AS NUM_ROWS
FROM syscat.tables WHERE TABSCHEMA not in ('SYSIBM', 'SYSPUBLIC', 'SYSCAT', 'SYSSTAT', 'SYSIBMADM', 'SYSTOOLS')
and TABSCHEMA in ('db2intuser') GROUP BY TABSCHEMA, TABNAME; |
| TOOLBOX |
| EDB Postgres | Perform an ANALYZE before forecast or deploy to get the most accurate values. | Permissions No special permissions required. Source Uses statistics from the catalog table pg_class. Sample Query | Code Block |
|---|
SELECT concat(relnamespace::regnamespace::text,'.',pgc.relname) as TABLE_NAME, pgc.reltuples::BIGINT AS NUM_ROWS
FROM pg_class pgc WHERE pgc.relkind='r' and relnamespace::regnamespace::text not in ('pg_catalog', 'information_schema', 'sys', 'public', 'pgagent')
and relnamespace::regnamespace::text IN ('SchemaNameHere'); |
| PostgreSQL wiki |
...