List tables with primary key-Db2

How to List tables with their primary keys in Db2?

Solution

SELECT TAB.TABSCHEMA AS SCHEMA_NAME,
    TAB.TABNAME AS TABLE_NAME,
    CONST.CONSTNAME AS PK_NAME,
    LISTAGG(KEY.COLNAME, ', ') 
            WITHIN GROUP(ORDER BY KEY.COLSEQ) AS COLUMN_NAME
FROM 
SYSCAT.TABLES TAB
LEFT OUTER JOIN SYSCAT.TABCONST CONST 
    ON CONST.TABSCHEMA = TAB.TABSCHEMA 
    AND CONST.TABNAME = TAB.TABNAME AND CONST.TYPE = 'P'
LEFT OUTER JOIN SYSCAT.KEYCOLUSE KEY 
    ON CONST.TABSCHEMA = KEY.TABSCHEMA 
    AND CONST.TABNAME = KEY.TABNAME
    AND CONST.CONSTNAME = KEY.CONSTNAME     
WHERE TAB.TYPE = 'T'
    AND TAB.TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TAB.TABSCHEMA, CONST.CONSTNAME, TAB.TABNAME   
ORDER BY TAB.TABSCHEMA, TAB.TABNAME

More Questions