Find tables without primary keys (PKs) in Teradata database

Query below lists tables in a database without primary keys.

Query

SELECT  t.DatabaseName,
        t.TableName
FROM    DBC.TablesV t
LEFT JOIN   DBC.IndicesV i
ON  t.DataBaseName = i.DataBaseName
AND t.TableName = i.TableName
AND i.IndexType = 'K'
WHERE   t.TableKind = 'T'
AND t.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 i.DatabaseName is null
GROUP BY    t.DatabaseName,
            t.TableName,
            i.IndexName
ORDER BY    t.DatabaseName,
            t.TableName;

Columns

  • DatabaseName - database name
  • TableName - table name

Rows

  • One row represents one table without primary key in a database
  • Scope of rows: all tables without primary keys in a database
  • Ordered by database and table name

Sample results

Below is a list of tables in Teradata databases without primary keys. Is that a lot? Check out here.

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