SQLクエリメモ - 表定義の情報を出力するクエリ

DB2のコントロール・センターで表を選択したときに下に表示される表定義の情報(＋外部キーの情報)を得るためのクエリ。
テキストとして得られればいろいろと使えるので。

SELECT
    CASE WHEN
            P.TABLE_SCHEM IS NOT NULL
            AND P.TABLE_NAME IS NOT NULL
            AND P.COLUMN_NAME IS NOT NULL
        THEN '*' ELSE '' END AS "PK",
    CAST(C.TABLE_NAME AS VARCHAR(8)) AS "TABLE_NAME",
    CAST(C.COLUMN_NAME AS VARCHAR(8)) AS "COLUMN_NAME",
    CAST(C.DATA_TYPE AS VARCHAR(22)) AS "DATA_TYPE",
    C.CHARACTER_OCTET_LENGTH AS "LEN",
    C.IS_NULLABLE AS "NULLABLE",
    CAST(F.PKTABLE_NAME || '(' || F.PKCOLUMN_NAME || ')' AS VARCHAR(20)) AS "FK"
FROM SYSIBM.COLUMNS AS C
LEFT OUTER JOIN SYSIBM.SQLFOREIGNKEYS AS F
ON C.TABLE_SCHEMA = F.FKTABLE_SCHEM
    AND C.TABLE_NAME = F.FKTABLE_NAME
    AND C.COLUMN_NAME = F.FKCOLUMN_NAME
LEFT OUTER JOIN SYSIBM.SQLPRIMARYKEYS AS P
ON P.TABLE_SCHEM = C.TABLE_SCHEMA
    AND P.TABLE_NAME = C.TABLE_NAME
    AND P.COLUMN_NAME = C.COLUMN_NAME
WHERE
    (C.TABLE_SCHEMA, C.TABLE_NAME)
        IN (SELECT TABLE_SCHEMA, TABLE_NAME
            FROM SYSIBM.TABLES
            WHERE TABLE_TYPE = 'BASE TABLE')
    AND C.TABLE_SCHEMA = CURRENT_SCHEMA
    AND C.TABLE_NAME NOT LIKE 'EXPLAIN_%'
ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION

CURRENT_SCHEMAの表のうち、'EXPLAIN_'で始まるものを除外。また出力幅を縮めるためにCASTしているが、長さの調整は適宜行う必要がある。
例えば‥

CREATE TABLE T_AAA (
    ID VARCHAR(8) NOT NULL,
    NAME VARCHAR(8) NOT NULL,
    PRIMARY KEY (ID)) ;
CREATE TABLE T_BBB (
    ID VARCHAR(8) NOT NULL,
    NAME VARCHAR(8) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (ID) REFERENCES T_AAA (ID)) ;
CREATE TABLE T_CCC (
    ID1 INT NOT NULL,
    ID2 VARCHAR(8) NOT NULL,
    ID VARCHAR(8) NOT NULL,
    NAME VARCHAR(32) NOT NULL,
    ADDR VARCHAR(64),
    COL1 SMALLINT,
    COL2 BIGINT,
    COL3 CHAR(8),
    COL4 CLOB,
    COL5 DATE,
    COL6 TIME,
    COL7 TIMESTAMP,
    COL8 XML,
    CONSTRAINT PKEY PRIMARY KEY (ID1, ID2),
    CONSTRAINT FKEY1 FOREIGN KEY (ID) REFERENCES T_AAA (ID),
    CONSTRAINT FKEY2 FOREIGN KEY (ID) REFERENCES T_BBB (ID)) ;

という表を作って上記のクエリを実行してみると‥

PK TABLE_NAME COLUMN_NAME DATA_TYPE              LEN         NULLABLE FK
-- ---------- ----------- ---------------------- ----------- -------- --------------------
*  T_AAA      ID          CHARACTER VARYING                8 NO       -
   T_AAA      NAME        CHARACTER VARYING                8 NO       -
*  T_BBB      ID          CHARACTER VARYING                8 NO       T_AAA(ID)
   T_BBB      NAME        CHARACTER VARYING                8 NO       -
*  T_CCC      ID1         INTEGER                          - NO       -
*  T_CCC      ID2         CHARACTER VARYING                8 NO       -
   T_CCC      ID          CHARACTER VARYING                8 NO       T_AAA(ID)
   T_CCC      ID          CHARACTER VARYING                8 NO       T_BBB(ID)
   T_CCC      NAME        CHARACTER VARYING               32 NO       -
   T_CCC      ADDR        CHARACTER VARYING               64 YES      -
   T_CCC      COL1        SMALLINT                         - YES      -
   T_CCC      COL2        BIGINT                           - YES      -
   T_CCC      COL3        CHARACTER                        8 YES      -
   T_CCC      COL4        CHARACTER LARGE OBJECT     1048576 YES      -
   T_CCC      COL5        DATE                             - YES      -
   T_CCC      COL6        TIME                             - YES      -
   T_CCC      COL7        TIMESTAMP                        - YES      -
   T_CCC      COL8        XML                              - YES      -

という感じ。
ひとつ難点があって、ひとつの列に対して外部キーが複数設定されていると、その数だけ列の情報が重複して出力されてしまう。文字列の集約関数があれば、GROUP BYを使って外部キーの情報をひとつにまとめられるのだが‥(形にこだわらなければ、XMLAGG関数を使う方法はある‥)

(余談)
ちなみに、最初は、

SELECT
    CASE WHEN EXISTS(
            SELECT *
            FROM SYSIBM.SQLPRIMARYKEYS AS P
            WHERE P.TABLE_SCHEM = C.TABLE_SCHEMA
                AND P.TABLE_NAME = C.TABLE_NAME
                AND P.COLUMN_NAME = C.COLUMN_NAME
        ) THEN '*' ELSE '' END AS "PK",
    CAST(C.TABLE_NAME AS VARCHAR(8)) AS "TABLE_NAME",
    CAST(C.COLUMN_NAME AS VARCHAR(8)) AS "COLUMN_NAME",
    CAST(C.DATA_TYPE AS VARCHAR(22)) AS "DATA_TYPE",
    C.CHARACTER_OCTET_LENGTH AS "LEN",
    C.IS_NULLABLE AS "NULLABLE",
    CAST(F.PKTABLE_NAME || '(' || F.PKCOLUMN_NAME || ')' AS VARCHAR(20)) AS "FK"
FROM SYSIBM.COLUMNS AS C
LEFT OUTER JOIN SYSIBM.SQLFOREIGNKEYS AS F
ON C.TABLE_SCHEMA = F.FKTABLE_SCHEM
    AND C.TABLE_NAME = F.FKTABLE_NAME
    AND C.COLUMN_NAME = F.FKCOLUMN_NAME
WHERE
    (C.TABLE_SCHEMA, C.TABLE_NAME)
        IN (SELECT TABLE_SCHEMA, TABLE_NAME
            FROM SYSIBM.TABLES
            WHERE TABLE_TYPE = 'BASE TABLE')
    AND C.TABLE_SCHEMA = CURRENT_SCHEMA
    AND C.TABLE_NAME NOT LIKE 'EXPLAIN_%'
ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION

というクエリを書いていたのだが、これだと、DB2 v9.1 で

SQL0582N  VALUES 文節、IN 述部、GROUP BY 文節または ORDER BY 文節の CASE
式に、比較述部、全選択を用いた IN 述部、または EXISTS
述部を含めることはできません。  SQLSTATE=42625

って怒られる‥