/* カラム定義情報を取得 */
SELECT
SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
,USER AS schema_name
,cols.TABLE_NAME AS table_name
,cols.COLUMN_NAME AS column_name
,cols.COLUMN_ID AS ordinal_position
/* PKか否かの判別フラグ */
,(CASE
WHEN pk_cols.CONSTRAINT_NAME IS NULL THEN
0
ELSE
1
END) AS is_primary_key
/* PK制約名 */
,pk_cols.CONSTRAINT_NAME AS pk_constraint_name
/* PK制約カラム位置 */
,pk_cols.KEY_ORDINAL AS pk_key_ordinal
/* UQか否かの判別フラグ */
,(CASE
WHEN uq_cols.CONSTRAINT_NAME IS NULL THEN
0
ELSE
1
END) AS is_unique_key
/* UQ制約名 */
,uq_cols.CONSTRAINT_NAME AS uq_constraint_name
/* UQ制約カラム位置 */
,uq_cols.KEY_ORDINAL AS uq_key_ordinal
,cols.DATA_TYPE AS column_data_type
,cols.DATA_DEFAULT AS column_default
,cols.NULLABLE AS is_nullable
,cols.DATA_LENGTH AS max_length
,cols.DATA_PRECISION AS precision
,cols.DATA_SCALE AS scale
,comments.COMMENTS AS column_comment
FROM
USER_TABLES tbls
INNER JOIN USER_TAB_COLUMNS cols
ON
cols.TABLE_NAME = tbls.TABLE_NAME
LEFT OUTER JOIN USER_COL_COMMENTS comments
ON
comments.TABLE_NAME = cols.TABLE_NAME
AND comments.COLUMN_NAME = cols.COLUMN_NAME
/* PK情報を結合 */
LEFT OUTER JOIN (
SELECT
cons_inner.CONSTRAINT_NAME AS constraint_name
,cons_columns_inner.POSITION AS key_ordinal
,tbls_inner.TABLE_NAME AS table_name
,cols_inner.COLUMN_NAME AS col_name
,cols_inner.COLUMN_ID AS col_id
FROM
USER_TABLES tbls_inner
INNER JOIN USER_TAB_COLUMNS cols_inner
ON
cols_inner.TABLE_NAME = tbls_inner.TABLE_NAME
INNER JOIN USER_CONSTRAINTS cons_inner
ON
cons_inner.OWNER = USER
AND cons_inner.CONSTRAINT_TYPE = 'P'
AND cons_inner.TABLE_NAME = tbls_inner.TABLE_NAME
INNER JOIN USER_CONS_COLUMNS cons_columns_inner
ON
cons_columns_inner.OWNER = cons_inner.OWNER
AND cons_columns_inner.CONSTRAINT_NAME = cons_inner.CONSTRAINT_NAME
AND cons_columns_inner.TABLE_NAME = cons_inner.TABLE_NAME
AND cons_columns_inner.COLUMN_NAME = cols_inner.COLUMN_NAME
) pk_cols
ON
pk_cols.TABLE_NAME = cols.TABLE_NAME
AND pk_cols.COL_NAME = cols.COLUMN_NAME
AND pk_cols.COL_ID = cols.COLUMN_ID
/* UQ情報を結合 */
LEFT OUTER JOIN (
SELECT
cons_inner.CONSTRAINT_NAME AS constraint_name
,cons_columns_inner.POSITION AS key_ordinal
,tbls_inner.TABLE_NAME AS table_name
,cols_inner.COLUMN_NAME AS col_name
,cols_inner.COLUMN_ID AS col_id
FROM
USER_TABLES tbls_inner
INNER JOIN USER_TAB_COLUMNS cols_inner
ON
cols_inner.TABLE_NAME = tbls_inner.TABLE_NAME
INNER JOIN USER_CONSTRAINTS cons_inner
ON
cons_inner.OWNER = USER
AND cons_inner.CONSTRAINT_TYPE = 'U'
AND cons_inner.TABLE_NAME = tbls_inner.TABLE_NAME
INNER JOIN USER_CONS_COLUMNS cons_columns_inner
ON
cons_columns_inner.OWNER = cons_inner.OWNER
AND cons_columns_inner.CONSTRAINT_NAME = cons_inner.CONSTRAINT_NAME
AND cons_columns_inner.TABLE_NAME = cons_inner.TABLE_NAME
AND cons_columns_inner.COLUMN_NAME = cols_inner.COLUMN_NAME
) uq_cols
ON
uq_cols.TABLE_NAME = cols.TABLE_NAME
AND uq_cols.COL_NAME = cols.COLUMN_NAME
AND uq_cols.COL_ID = cols.COLUMN_ID
ORDER BY
table_name, ordinal_position
/* ビュー定義を取得 */
SELECT
SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name
,USER AS schema_name
,views.VIEW_NAME AS view_name
,views.TEXT AS definition
FROM
USER_VIEWS views
ORDER BY
views.VIEW_NAME
