The number of rows in a table using table statistics

Database	SQL	Commands for refresh statistics

Database	SQL	Commands for refresh statistics
Oracle	select num_rows from dba_tables where owner = 'DEMO01' and table_name = 'TAB05_PART'; View name: dba_tables or all_tables	Compute for a table: begin dbms_stats.gather_table_stats(ownname => 'DEMO01' , tabname => 'TAB05_PART', degree => 4); end; Compute for a schema: begin dbms_stats.gather_schema_stats(ownname => 'SAMPLE1', degree => 2); end;
Microsoft SQL	SELECT row_count FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('test.tab_statistic'); OR with database name SELECT row_count FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('Acc_lhost_MSSQL2_lhost_SQLEXPRESS.test.tab_statistic');	Compute for a table: UPDATE STATISTICS test.tab_statistic WITH FULLSCAN; Compute for all tables in the current database: EXEC sp_updatestats;
MySQL	select table_rows from INFORMATION_SCHEMA.TABLES where table_schema='MASKING_TEST' and table_name='test_stat';	Compute for a table: analyze table MASKING_TEST.test_stat; Analyzing multiple tables: analyze table MASKING_TEST.T1, TABLE_50K;
PostgreSQL	SELECT reltuples::bigint AS estimate FROM pg_class WHERE oid = 'test."TEST_STAT"'::regclass;	Compute for a table: analyze MASKING_TEST.test_stat; Compute for all tables in the current database: analyze [verbose];
DB2 LUW	SELECT CARD FROM syscat.tables WHERE tabschema='TEST' AND tabname='TEST_STAT';	Compute for a table: CALL SYSPROC.ADMIN_CMD ('RUNSTATS ON TABLE TEST.TEST_STAT');
SAP HANA Database	SELECT RECORD_COUNT FROM "SYS"."M_TABLES" WHERE SCHEMA_NAME = 'TEST1' AND TABLE_NAME = 'TAB03'	Looks like HANA database refreshing row count statistic automatically. Checked this after INSERT, DELETE, TRUNCATE, CREATE TABLE AS SELECT, IMPORT operations.
DB2 z/os	SELECT CARDF FROM SYSIBM.SYSTABLES WHERE CREATOR = 'TEST1' AND NAME = 'TAB06';	Compute for a table: CALL SYSPROC.DSNUTILU('RUNSTAT','NO','TEMPLATE TEMPL1 DSN IBMUSER.SYSUT1 UNIT SYSDA TEMPLATE TEMPL2 DSN IBMUSER.SORTOUT UNIT SYSDA TEMPLATE TEMPL3 DSN IBMUSER.SYSERR3 UNIT SYSDA RUNSTATS TABLESPACE(DSN03248.TAB06) TABLE(ALL) TABLESAMPLE SYSTEM AUTO UPDATE ALL ',1); where DSN03248.TAB06 = SELECT DBNAME\|\|'.'\|\|TSNAME FROM SYSIBM.SYSTABLES WHERE CREATOR = 'TEST1' AND NAME = 'TAB06';

Examples of the behavior of the relevance of statistics in different databases:

Oracle database

drop table sample1.tab1 purge;
create table sample1.tab1(id number);

insert into sample1.tab1 values(1);
commit;

select num_rows from dba_tables where owner = 'SAMPLE1' and table_name = 'TAB1';
--Result: NULL !!!

begin
  dbms_stats.gather_schema_stats(ownname => 'SAMPLE1', degree => 2);
end;
/

select num_rows from dba_tables where owner = 'SAMPLE1' and table_name = 'TAB1';
-- 1

Microsoft SQL

USE Acc_lhost_MSSQL2_lhost_SQLEXPRESS;

drop table test.tab_statistic; 

CREATE TABLE test.tab_statistic (
	ID int NULL
);

SELECT row_count FROM sys.dm_db_partition_stats   
WHERE object_id = OBJECT_ID('test.tab_statistic');  
-- Result : 0

insert into test.tab_statistic  values(1);

SELECT row_count FROM sys.dm_db_partition_stats   
WHERE object_id = OBJECT_ID('test.tab_statistic');  
-- Result: 1

--Or with database name
SELECT row_count FROM sys.dm_db_partition_stats   
WHERE object_id = OBJECT_ID('Acc_lhost_MSSQL2_lhost_SQLEXPRESS.test.tab_statistic');  
-- Result: 1

truncate table test.tab_statistic; 
SELECT row_count FROM sys.dm_db_partition_stats   
WHERE object_id = OBJECT_ID('test.tab_statistic');  
-- Result: 0

-- Insert 5000 rows:
DECLARE @cnt INT = 0
BEGIN TRAN
WHILE @cnt < 5000
BEGIN
   insert into test.tab_statistic  values(@cnt)
   SET @cnt = @cnt + 1
END
COMMIT TRAN
GO

SELECT row_count FROM sys.dm_db_partition_stats   
WHERE object_id = OBJECT_ID('test.tab_statistic');
--Result: 5000


UPDATE STATISTICS test.tab_statistic WITH FULLSCAN;

use Acc_lhost_MSSQL2_lhost_SQLEXPRESS;
EXEC sp_updatestats;

MySQL

drop table MASKING_TEST.test_stat;

create table MASKING_TEST.test_stat (
	ID INT NULL
);

select table_rows from INFORMATION_SCHEMA.TABLES
where table_schema='MASKING_TEST' and table_name='test_stat';
## Result: 0

insert into MASKING_TEST.test_stat values (1);

select table_rows from INFORMATION_SCHEMA.TABLES
where table_schema='MASKING_TEST' and table_name='test_stat';
# Result: 1

drop procedure insrt_test_stat;

# Insert 1000 rows
DROP PROCEDURE IF EXISTS insert_test_stat;
DELIMITER //  
CREATE PROCEDURE insert_test_stat()   
BEGIN
DECLARE i INT DEFAULT 1; 
START TRANSACTION;
WHILE (i <= 1000) DO
    insert into  test_stat values(i);
    SET i = i+1;
END WHILE;
COMMIT;
END;
//  
DELIMITER ;

call insert_test_stat;

select table_rows from INFORMATION_SCHEMA.TABLES
where table_schema='MASKING_TEST' and table_name='test_stat';
# Result: 1001

truncate test_stat;

select table_rows from INFORMATION_SCHEMA.TABLES
where table_schema='MASKING_TEST' and table_name='test_stat';
# Result: 0

analyze table MASKING_TEST.test_stat;

Data Anonymization Module Documents

The number of rows in a table using table statistics

Examples of the behavior of the relevance of statistics in different databases:

Oracle database

Microsoft SQL

MySQL

PostgreSQL

DB2 LUW

SAP HANA Database

DB2 z/OS