タイトル: 特定スキーマの全テーブルの全カラム情報を取得する方法
SEOタイトル: 【DB2】特定スキーマの全テーブルの全カラム情報を取得する方法
| この記事の要点 |
|
結論: SYSCAT.COLUMNS をスキーマ名で絞る
Db2 for Linux, UNIX and Windows（LUW）では、「SYSCAT.COLUMNS」カタログビューにデータベース内のすべてのカラム情報が格納されています。TABSCHEMA カラムにスキーマ名を条件として指定することで、対象のスキーマを絞ることができます。
SELECT * FROM SYSCAT.COLUMNS WHERE TABSCHEMA = 'SCHEMA_NAME'これで、指定したスキーマにあるすべての表・ビューの、すべてのカラムが 1 行ずつ返ります。
実用的なクエリ: 必要な列に絞って並べる
SELECT * では列が多すぎて読みにくいため、テーブル定義書を作るような用途では次のように列を選び、テーブル名とカラムの順番で並べ替えます。
SELECT
TABNAME,
COLNO,
COLNAME,
TYPENAME,
LENGTH,
SCALE,
NULLS,
DEFAULT,
KEYSEQ,
REMARKS
FROM SYSCAT.COLUMNS
WHERE TABSCHEMA = 'SCHEMA_NAME'
ORDER BY TABNAME, COLNO主な列の意味
| 列名 | 内容 |
|---|---|
| TABSCHEMA | スキーマ名 |
| TABNAME | 表（またはビュー）の名前 |
| COLNAME | カラム名 |
| COLNO | 表の中でのカラムの位置（0 から始まる） |
| TYPENAME | データ型名（VARCHAR、INTEGER、DECIMAL、TIMESTAMP など） |
| LENGTH | 長さ。DECIMAL の場合は精度（全体の桁数） |
| SCALE | DECIMAL の小数部の桁数 |
| NULLS | NULL を許可するなら 'Y'、NOT NULL なら 'N' |
| DEFAULT | デフォルト値（未設定なら NULL） |
| KEYSEQ | 主キーを構成する列なら、その中での順番。主キーでなければ NULL |
| IDENTITY | ID 列（自動採番）なら 'Y' |
| REMARKS | COMMENT ON COLUMN で付けたコメント |
条件を追加して絞り込む
特定のテーブルだけ
SELECT COLNO, COLNAME, TYPENAME, LENGTH, SCALE, NULLS
FROM SYSCAT.COLUMNS
WHERE TABSCHEMA = 'SCHEMA_NAME'
AND TABNAME = 'TABLE_NAME'
ORDER BY COLNOビューを除いて表だけ
SYSCAT.COLUMNS にはビューのカラムも含まれます。表だけにしたい場合は SYSCAT.TABLES と結合し、TYPE が 'T'（表）のものに絞ります。
SELECT c.TABNAME, c.COLNO, c.COLNAME, c.TYPENAME, c.LENGTH, c.NULLS
FROM SYSCAT.COLUMNS c
JOIN SYSCAT.TABLES t
ON t.TABSCHEMA = c.TABSCHEMA
AND t.TABNAME = c.TABNAME
WHERE c.TABSCHEMA = 'SCHEMA_NAME'
AND t.TYPE = 'T'
ORDER BY c.TABNAME, c.COLNOSYSCAT.TABLES の TYPE は、'T' が表、'V' がビュー、'A' が別名（エイリアス）などを表します。
特定のカラム名を持つテーブルを探す
SELECT TABSCHEMA, TABNAME, COLNAME, TYPENAME
FROM SYSCAT.COLUMNS
WHERE COLNAME LIKE '%CUSTOMER_ID%'
ORDER BY TABSCHEMA, TABNAME影響調査で「この項目を使っている表はどれか」を調べるときに便利です。
落とし穴
- 大文字・小文字: Db2 は引用符なしで作成した名前を大文字で格納します。
TABSCHEMA = 'myschema'のように小文字で書くと 0 件になるので、大文字で指定するかUPPER()を使います - 引用符付きで作られた名前:
CREATE TABLE "MyTable"のように引用符付きで作成した場合は、大文字小文字がそのまま保存されます。0 件のときはUPPER(TABNAME) LIKE '%MYTABLE%'のように大文字にそろえた部分一致で実際の名前を確認してください - システムスキーマ: スキーマを指定しないと SYSCAT や SYSIBM などのシステムカタログのカラムまで大量に返ります
- z/OS 版との違い: Db2 for z/OS には SYSCAT ビューがなく、
SYSIBM.SYSCOLUMNSを使います。列名も TBCREATOR（スキーマ）、TBNAME（表名）、NAME（カラム名）、COLTYPE（型）のように異なります
SQL 以外で確認する方法
コマンド行プロセッサ（CLP）からなら、1 つのテーブルの定義は DESCRIBE で確認できます。
db2 connect to SAMPLE
db2 "DESCRIBE TABLE SCHEMA_NAME.TABLE_NAME"CREATE TABLE 文（DDL）として出力したい場合は db2look コマンドを使います。詳しくは テーブル定義からDDL(CREATE TABLE)を生成する方法 を参照してください。