List tables with the largest number of columns in Teradata database

Query that returns tables with number of columns, ordered from the ones that have the most.

Query

SELECT  C.DatabaseName,
        C.TableName,
        COUNT(*) as Columns
FROM    DBC.ColumnsV C
JOIN    DBC.TablesV T
ON      C.TableName = T.TableName
AND     C.DatabaseName = T.DatabaseName
AND     T.TableKind = 'T'
WHERE   C.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')
GROUP BY    C.DatabaseName,
            C.TableName
ORDER BY COUNT(*) DESC;

Columns

  • DatabaseName - name of the DATABASE
  • TableName - name of the table
  • Columns - number of columns in table

Rows

  • One row represents one table in a database
  • Scope of rows: all tables in a database
  • Ordered by number of columns descending - from tables with the most columns

Sample results

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