List tables by their size in Teradata database

Query below returns tables in a database with space they use ordered from the ones using most.

Query

SELECT  tbl.DatabaseName,
        tbl.TableName,
        SUM(spc.CurrentPerm)/1024.00 as TableSize
FROM    DBC.TablesV tbl
JOIN    DBC.TableSize spc
ON  tbl.DatabaseName = spc.DatabaseName
AND tbl.TableName = spc.TableName
WHERE   tbl.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')
AND TableKind = 'T'
GROUP BY    tbl.DatabaseName,
            tbl.TableName
ORDER BY TableSize DESC;

Columns

  • DatabaseName - name of the database
  • table_name - name of the table
  • table_size - space used by table in MB

Rows

  • One row represents one table in a database
  • Scope of rows: all tables in a database
  • Ordered by table used size from largest to smallest

Sample results

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