List tables with the largest number of columns in Teradata database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept