All Forums Database
teradatatester 69 posts Joined 01/10
22 Aug 2011
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.

mnylin 118 posts Joined 12/09
23 Aug 2011

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.

teradatatester 69 posts Joined 01/10
23 Aug 2011

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” .

You must sign in to leave a comment.