【Oracle】テーブル定義情報を確認する方法(テーブル名やカラム名、データ型で検索)

テーブル定義とはテーブルの詳細情報のことで、どのようなカラムが存在して、各カラムの属性は何かを定義している情報です。

Oracleでカラム情報などのテーブル定義を確認する方法をまとめました。MySQLとは全然違うのでよく間違えることも多々あるのでここにメモしておきます。

ちなみ余談ですが、MySQLではテーブル定義を取得するときは、以下のSQLを実行するだけで取得できます。

show create table [スキーマ].[テーブル名];

Oracleにはそもそもそshow create文は存在しないので別の方法で取得します。

 

Oracleでテーブル定義を確認する

例えば以下のようなテーブルを参考にします。

テーブル名:SAMPLE_TABLE

ID NAME NAME_KANA TEL MAIL 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 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
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 MAIL 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

各カラムの意味について、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
    HEIGHT BALANCED

by ALL_TAB_COLUMNS

 

便利な使い方

補足として、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';

 



投稿日:2019-07-06    更新日:2020-08-13

[スポンサーリンク]

関連記事
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
サイト内検索
プロフィール

プロフィール

[Name : じゃぶじゃぶ(@jbjb_2019)]
都内で社内SEをしているおじさん。
仕事で得られる知識だけでは限界を感じ、 WEBの勉強がてらITブログを開始。
サーバからWEBサイトまでフルスクラッチで開発しました。
現在は勉強のモチベーションを保つために活用中。
興味があることを雑記的に書いていきます。

[スポンサーリンク]

[スポンサードリンク]

最近の記事