Oracleテーブルの情報取得

2月 9th, 2011

キー、型、サイズとか。

SELECT    t.TABLE_NAME,
        t.COLUMN_NAME,
        p.CONSTRAINT_TYPE,
        p.POSITION,
        t.DATA_TYPE,
        t.DATA_LENGTH,
        t.NULLABLE
FROM    USER_TAB_COLUMNS t,
        (SELECT    c.TABLE_NAME,
                s.COLUMN_NAME,
                c.CONSTRAINT_TYPE,
                s.POSITION
        FROM    USER_CONSTRAINTS c,
                USER_CONS_COLUMNS s
        WHERE    c.CONSTRAINT_TYPE = 'P'
        AND        c.CONSTRAINT_NAME = S.CONSTRAINT_NAME
        AND        c.TABLE_NAME = 'テーブル名'
        ORDER BY c.CONSTRAINT_NAME,S.POSITION) p
WHERE    t.TABLE_NAME = 'テーブル名'
AND        t.TABLE_NAME = p.TABLE_NAME(+)
AND        t.COLUMN_NAME = p.COLUMN_NAME(+)
ORDER BY t.COLUMN_ID

Posted in ProgramingNo Comments

Leave a Comment