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