Find empty tables in Teradata database

This query returns list of tables in a database without any rows.

Query

SELECT  DatabaseName,
        TableName,
        LastCollectTimeStamp
FROM    DBC.TableStatsV
WHERE   IndexNumber = 1
AND RowCount = 0
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    DatabaseName,
            TableName DESC;

Columns

  • DatabaseName - name of the database
  • TableName - name of the table
  • LastCollectTimeStamp - date when stats were collected

Rows

  • One row represents one table
  • Scope of rows: only empty tables (without rows) at stats collection time
  • Ordered by database name and table name

Sample results

0
There are no comments. Click here to write the first comment.