Tried TRUNCATE - no errors. Tried DELETE - 0 rows affected.
But I probably found the reason, this is likely to be a cloned database, that was created with DBCC CLONEDATABASE. This server is not production, this is performance test server and preparation for production deployment.
I reproduced this situation, cloned another existing database, ran my queries that select from sysindexes/partitions/partition_stats/alloc_units, they all show that tables have records, but select * from actual tables show nothing.
What happened I assume is some of my colleagues cloned database from production, backed it up, restored to this server with replace and move to existing database, and this is where I got confused. I assumed that tables have records, and this is supported by all my queries from sys views, but they actually all empty.
But the bigger problem that there is no way to differentiate between real database and its clone. I tried to compare the entries in sys.databases and could not find any difference.