How many tables don't have primary keys (with percentage) in Teradata database

This query listed tables without primary keys and this one shows how many of them there are and what is the percentage of total tables.

Query

SELECT  COUNT(*) AS Tables,
       COUNT(*) - COUNT(i.TableName) AS NoPKTables,
       100.0*(COUNT(*) - COUNT(i.TableName)) / COUNT(*) AS NoPKPercent
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');

Columns

  • Tables - number of all tables in a database
  • NoPKTables - number of tables without a primary key
  • NoPKPercent - percentage of tables without primary key in all tables

Rows

Query returns just one row.

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.