List tables with their primary keys (PKs) in Teradata database

Query below lists tables and their primary key (PK) constraint names. By browsing list you can spot which tables have and which don't have primary keys.

See also: tables without primary keys.


SELECT  t.DatabaseName,
        TRIM(TRAILING ',' 
            FROM XMLAGG(i.columnName || ',' 
                 ORDER BY i.columnPosition)(varchar(100))) AS KeyColumns
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',
    'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
GROUP BY t.DatabaseName,
ORDER BY t.DatabaseName,


  • DatabaseName - database name
  • TableName - table name
  • IndexName - primary key index name
  • KeyColumns - list of PK columns separated with ','


  • One row represents one table in a database
  • Scope of rows: all tables in a database
  • Ordered by schema name, table name

Sample results

You can see what are the names of PK constraints for each table and which tables don't have PKs at all.

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free

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