いろいろ備忘録日記

主に .NET とか Go とか Flutter とか Python絡みのメモを公開しています。

オラクルでの定義情報の取得 (テーブル定義, カラム定義など)(oracle, user_tables, user_tab_columns, user_views, user_synonyms, user_sequences, user_procedures, user_source)


忘れない内にメモメモ。


[テーブル定義]

/* テーブル定義情報を取得 */
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


================================
過去の記事については、以下のページからご参照下さい。

サンプルコードは、以下の場所で公開しています。