Table or view that contains the NumberOfRows & NumberOfColumns?

Database
Junior Supporter

Table or view that contains the NumberOfRows & NumberOfColumns?

Is there a table or view that contains the NumberOfRows, NumberOfColumns, DataBaseName, TableName?

In Oracle its called all_tables and in Netezza its called _v_table_only_storage_stat.

Here is where I looked:
DBC.ColumnStats
DBC.MultiColumnStats
DBC.IndexStats
DBC.TABLESV

This returns DataBaseName, TableName and when the tables were created or altered:

SELECT
DataBaseName
,TableName
,CreateTimeStamp
,LastAlterTimeStamp
FROM DBC.TABLESV

Now I just need a way to get the NumberOfRows & NumberOfColumns for each table.
2 REPLIES
Enthusiast

Re: Table or view that contains the NumberOfRows & NumberOfColumns?

Column information is stored in DBC.Columns. SELECT DatabaseName, TableName, COUNT(*) AS NumberOfColumns FROM DBC.Columns GROUP BY 1,2; will give you the NumberofColumns. DBC doesn't store row counts anywhere. DBC.TableSize shows the space used but not number of rows. You'll have to do a COUNT(*) from a table to determine that.
Junior Supporter

Re: Table or view that contains the NumberOfRows & NumberOfColumns?

Thanks mnylin!

That is disappointing, auto tracking of NumberOfRows seems to be pretty standard with other databases.

Another example in MySQL, SHOW TABLE STATUS returns “Rows” .