In Teradata to identify row number of table, firstly we need to collect statistic by COLLECT STATISTICS statement

Example

COLLECT STATISTICS INDEX(address_id) ON Person.Address ;

To read more visit official documentation

Note

This query may not return exact row number and can be very diffrent from real result, because it depends on collect statistics time.

Query below returns list of tables in a database with their number of rows at the time statistic was collected.

Query

SELECT  DatabaseName,
        TableName,
        RowCount,
        LastCollectTimeStamp
FROM    DBC.TableStatsV
WHERE   IndexNumber = 1
AND DataBaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
    'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
    'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
    'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB',  'TDStats',
    'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
    'tdwm',  'SQLJ', 'TD_SYSFNLIB',  'SYSSPATIAL')
ORDER BY    RowCount DESC;

Columns

• DatabaseName - database name
• TableName - name of the table
• RowCount - number of rows in a table at stats collect time
• LastCollectTimeStamp - date when stats were collected

Rows

• One row represents one table
• Scope of rows: all tables on which stats was collected in a database
• Ordered by number of rows descending

Sample results