忘れない内にメモメモ。
[テーブル定義]
/* テーブル定義情報を取得 */ SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name ,USER AS schema_name ,tbls.TABLE_NAME AS table_name ,comments.TABLE_TYPE AS table_type ,comments.COMMENTS AS table_comment FROM USER_TABLES tbls LEFT OUTER JOIN USER_TAB_COMMENTS comments ON tbls.TABLE_NAME = comments.TABLE_NAME ORDER BY tbls.TABLE_NAME
[カラム定義]
/* カラム定義情報を取得 */ 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
[トリガー定義]
/* トリガー定義の取得 */ SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name ,USER AS schema_name ,triggers.TRIGGER_NAME AS trigger_name ,source.LINE AS line ,source.TEXT AS definision FROM USER_TRIGGERS triggers INNER JOIN USER_SOURCE source ON source.NAME = triggers.TRIGGER_NAME AND source.TYPE = 'TRIGGER' ORDER BY trigger_name, line
[シノニム定義]
/* シノニム情報を取得 */ SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name ,USER AS schema_name ,synonyms.SYNONYM_NAME AS synonym_name ,synonyms.TABLE_NAME AS base_object_name FROM USER_SYNONYMS synonyms ORDER BY synonyms.SYNONYM_NAME
[シーケンス定義]
/* シーケンスの定義情報を取得 */ SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name ,USER AS schema_name ,sequences.SEQUENCE_NAME AS sequence_name ,sequences.MIN_VALUE AS min_value ,sequences.MAX_VALUE AS max_value ,sequences.INCREMENT_BY AS increment_by ,sequences.CYCLE_FLAG AS cycle_flag ,sequences.ORDER_FLAG AS order_flag ,sequences.CACHE_SIZE AS cache_size ,sequences.LAST_NUMBER AS last_number FROM USER_SEQUENCES sequences ORDER BY sequences.SEQUENCE_NAME
[プロシージャ定義]
/* プロシージャ定義の取得 */ SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name ,USER AS schema_name ,procedures.PROCEDURE_NAME AS procedure_name ,source.LINE AS line ,source.TEXT AS definision FROM USER_PROCEDURES procedures INNER JOIN USER_SOURCE source ON source.NAME = procedures.PROCEDURE_NAME AND source.TYPE = 'PROCEDURE' ORDER BY procedure_name, line
[ファンクション定義]
/* ファンクション定義の取得 */ SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name ,USER AS schema_name ,procedures.PROCEDURE_NAME AS procedure_name ,source.LINE AS line ,source.TEXT AS definision FROM USER_PROCEDURES procedures INNER JOIN USER_SOURCE source ON source.NAME = procedures.PROCEDURE_NAME AND source.TYPE = 'FUNCTION' ORDER BY procedure_name, line
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
過去の記事については、以下のページからご参照下さい。
- いろいろ備忘録日記まとめ
サンプルコードは、以下の場所で公開しています。
- いろいろ備忘録日記サンプルソース置き場
