テーブル定義とはテーブルの詳細情報のことで、どのようなカラムが存在して、各カラムの属性は何かを定義している情報です。
Oracleでカラム情報などのテーブル定義を確認する方法をまとめました。MySQLとは全然違うのでよく間違えることも多々あるのでここにメモしておきます。
ちなみ余談ですが、MySQLではテーブル定義を取得するときは、以下のSQLを実行するだけで取得できます。
show create table [スキーマ].[テーブル名];
Oracleにはそもそもそshow create文は存在しないので別の方法で取得します。
Oracleでテーブル定義を確認する
例えば以下のようなテーブルを参考にします。
テーブル名：SAMPLE_TABLE
| ID | NAME | NAME_KANA | TEL | BIRTHDAY | |
|---|---|---|---|---|---|
| 1 | 太郎 | TARO | 09000000001 | taro@sample.com | 1986-01-01 |
| 2 | 次郎 | JIRO | 09000000002 | jiro@sample.com | 1999-01-02 |
| 3 | 花子 | HANAKO | 09000000003 | hanako@sample.com | 2019-01-03 |
上記のテーブルのテーブル定義を取得したい場合は以下のようなSQLを実行します。
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'SAMPLE_TABLE';
SQLの「TABLE_NAME = 'SAMPLE_TABLE'」の部分を確認したいテーブル名に変更すれば、ピンポイントでテーブル定義を確認できます。
クエリの結果として、表形式で結果を受け取ることが可能です。
| OWNER | TABLE_NAME | COLUMN_NAME | DATA_TYPE | DATA_TYPE_MOD | DATA_TYPE_OWNER | DATA_LENGTH | DATA_PRECISION | DATA_SCALE | NULLABLE | COLUMN_ID | DEFAULT_LENGTH | DATA_DEFAULT | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | LAST_ANALYZED | SAMPLE_SIZE | CHARACTER_SET_NAME | CHAR_COL_DECL_LENGTH | GLOBAL_STATS | USER_STATS | AVG_COL_LEN | CHAR_LENGTH | CHAR_USED | V80_FMT_IMAGE | DATA_UPGRADED | HISTOGRAM |
| ADMIN | SAMPLE_TABLE | NAME | NVARCHAR2 | (NULL) | (NULL) | 100 | (NULL) | (NULL) | N | 3 | (NULL) | (NULL) | 893568 | (VARBYTES) | (VARBYTES) | 0.00000440482063570371 | 0 | 254 | 2019/07/11 20:00:00 | 21026 | NCHAR_CS | 50 | YES | NO | 12 | 50 | C | NO | YES | HEIGHT BALANCED |
| ADMIN | SAMPLE_TABLE | NAME_KANA | NVARCHAR2 | (NULL) | (NULL) | 100 | (NULL) | (NULL) | Y | 4 | (NULL) | (NULL) | 590848 | (VARBYTES) | (VARBYTES) | 0.0000127682937729031 | 352276 | 254 | 2019/07/11 20:00:00 | 17731 | NCHAR_CS | 50 | YES | NO | 13 | 50 | C | NO | YES | HEIGHT BALANCED |
| ADMIN | SAMPLE_TABLE | TEL | VARCHAR2 | (NULL) | (NULL) | 15 | (NULL) | (NULL) | Y | 6 | (NULL) | (NULL) | 311264 | (VARBYTES) | (VARBYTES) | 0.0000106343382783006 | 1409612 | 254 | 2019/07/11 20:00:00 | 7732 | CHAR_CS | 15 | YES | NO | 6 | 15 | B | NO | YES | HEIGHT BALANCED |
| ADMIN | SAMPLE_TABLE | VARCHAR2 | (NULL) | (NULL) | 100 | (NULL) | (NULL) | Y | 7 | (NULL) | (NULL) | 44316 | (VARBYTES) | (VARBYTES) | 0.0000225652134669194 | 2198199 | 1 | 2019/07/11 20:00:00 | 47550 | CHAR_CS | 100 | YES | NO | 2 | 100 | B | NO | YES | NONE | |
| ADMIN | SAMPLE_TABLE | BIRTHDAY | DATE | (NULL) | (NULL) | 7 | (NULL) | (NULL) | Y | 9 | (NULL) | (NULL) | 26554 | (VARBYTES) | (VARBYTES) | 0.0000376591097386458 | 635903 | 1 | 2019/07/11 20:00:00 | 1609846 | (NULL) | (NULL) | YES | NO | 7 | 0 | (NULL) | NO | YES | NONE |
| ADMIN | SAMPLE_TABLE | ID | NUMBER | (NULL) | (NULL) | 22 | 15 | 0 | N | 1 | (NULL) | (NULL) | 2245749 | (VARBYTES) | (VARBYTES) | 0.000000445285737631409 | 0 | 1 | 2019/07/11 20:00:00 | 2245749 | (NULL) | (NULL) | YES | NO | 6 | 0 | (NULL) | NO | YES | NONE |
各カラムの意味について、Oracle社のHPに詳細があったの、併せて載せておきます。
| 列 | データ型 | NULL | 説明 |
|---|---|---|---|
| OWNER | VARCHAR2(30) | NOT NULL | 表、ビューまたはクラスタの所有者 |
| TABLE_NAME | VARCHAR2(30) | NOT NULL | 表、ビューまたはクラスタの名前 |
| COLUMN_NAME | VARCHAR2(30) | NOT NULL | 列名 |
| DATA_TYPE | VARCHAR2(106) | 列のデータ型 | |
| DATA_TYPE_MOD | VARCHAR2(3) | 列のデータ型修飾子 | |
| DATA_TYPE_OWNER | VARCHAR2(30) | 列のデータ型の所有者 | |
| DATA_LENGTH | NUMBER | NOT NULL | 列の長さ(バイト) |
| DATA_PRECISION | NUMBER | NUMBERデータ型の場合は10進精度。FLOATデータ型の場合は2進精度。その他のデータ型の場合はNULL。 | |
| DATA_SCALE | NUMBER | ||
| NULLABLE | VARCHAR2(1) | 列にNULLを指定できるかどうかを示す。列にNOT NULL制約がある場合、または列がPRIMARY KEYの一部である場合、 値はNとなる。この制約は、ENABLE VALIDATE状態である必要がある。 |
|
| COLUMN_ID | NUMBER | 作成された列の順序番号 | |
| DEFAULT_LENGTH | NUMBER | 列のデフォルト値の長さ | |
| DATA_DEFAULT | LONG | 列のデフォルト値 | |
| NUM_DISTINCT | NUMBER | 列内で異なる値の数 | |
| LOW_VALUE | RAW(32) | 列内の下限値脚 | |
| HIGH_VALUE | RAW(32) | 列内の上限値脚 | |
| DENSITY | NUMBER | COLUMN_NAMEに対してヒストグラムが使用可能な場合、この列にはヒストグラム内の1つ以下のエンドポイントに わたる値の選択性が示される。複数のエンドポイントにわたる値の選択性は示されない。 COLUMN_NAMEに対してヒストグラムが使用可能ではない場合、この列の値は1/NUM_DISTINCTである. |
|
| NUM_NULLS | NUMBER | 列内のNULLの数 | |
| NUM_BUCKETS | NUMBER | 列のヒストグラム内のバケット数 注意: ヒストグラム内のバケット数は、SQL文ANALYZEのSIZEパラメータに指定されます。ただし、 サンプル内の行数より多いバケットを持つヒストグラムは作成されません。 また、サンプルに繰返しが非常に多い値が含まれる場合、指定された数のバケットは作成されますが、 この列で指定した値は、内部圧縮アルゴリズムのために小さくなる場合があります。 |
|
| LAST_ANALYZED | DATE | この列が分析された最新の日付 | |
| SAMPLE_SIZE | NUMBER | この列の分析で使用されたサンプル・サイズ | |
| CHARACTER_SET_NAME | VARCHAR2(44) | キャラクタ・セットの名前 例₎CHAR_CS NCHAR_CS |
|
| CHAR_COL_DECL_LENGTH | NUMBER | キャラクタ・タイプ列の宣言の長さ | |
| GLOBAL_STATS | VARCHAR2(3) | パーティション表の場合、表全体を収集した列統計情報なのか(YES)、 基礎となるパーティションおよびサブパーティションの統計情報から推定されたものなのか(NO) |
|
| USER_STATS | VARCHAR2(3) | 統計情報が、ユーザーによって直接入力されたか(YES)されていないか(NO) | |
| AVG_COL_LEN | NUMBER | 列の平均の長さ(バイト) | |
| CHAR_LENGTH | NUMBER | 列の長さが文字単位で表示されます。この値は、次のデータ型のみに適用される。 例₎CHAR VARCHAR2 NCHAR NVARCHAR2 |
|
| CHAR_USED | VARCHAR2(1) | 列がバイトの長さセマンティクス(B)を使用するか、文字の長さセマンティクス(C)を使用するか、 またはデータ型が次のいずれでもない(NULL)ことを示す. CHAR VARCHAR2 NCHAR NVARCHAR2 |
|
| V80_FMT_IMAGE | VARCHAR2(3) | 列データがリリース8.0のイメージ形式であるかどうか(YES | NO) | |
| DATA_UPGRADED | VARCHAR2(3) | 列データが最新のタイプ・バージョン形式にアップグレードされたかどうか(YES | NO) | |
| HISTOGRAM | VARCHAR2(15) | ヒストグラムの有無およびタイプ: 例₎NONE FREQUENCY |
便利な使い方
補足として、Oracleでテーブル定義を取るときの便利な使い方をご紹介します。
MySQLと違いOracleではテーブル定義情報を専用のテーブルで持っている関係上、そのテーブルに対してもWhere句で検索をかけることが可能です。
例えば、上記のサンプルはテーブル単位で取得していますが、以下のようなSQLを実行するとOracleのユーザ単位でテーブル定義を取得できます。
SELECT * FROM ALL_TAB_COLUMNS WHERE OWNER = 'ADMIN';
さらに、テーブル名が「M_」で始まるテーブルを抽出したい場合は下記のようなSQLを使います。
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE 'M_%';
テーブル内にコードなどのカラムが存在していないか知りたいときは、カラム名に「CD」が含まれるものを検索すればよいでしょう。
SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%CD%';
他にも、特定のテーブル（下記例ではTABLE01とTABLE02）に対して、データ型が日付型のカラムを検索する、なんてことも可能です。
SELECT * FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME IN ('TABLE01','TABLE02') AND DATA_TYPE = 'DATE';
以上です。