LoginSignup
53
54

More than 5 years have passed since last update.

@ota-meshi(ota)inフューチャー株式会社

Oracle・PostgreSQL・MySQL・SQLServer・H2Databaseそれぞれでテーブル・カラム・プライマリキー・インデックスを取得するSQL

Last updated at Posted at 2015-12-08

Oracle・PostgreSQL・MySQL・SQLServer・H2Databaseで、TABLE一覧・COLUMN一覧・PRIMARY KEY・INDEX一覧を取得するSQL(?)を最近調べたのでさらすことにします。
Oracle以外は経験浅いので、間違っていたら教えてほしいです。

JavaからJDBC経由で取りたいのでPostgreSQLでコマンド「\d」でテーブル一覧が取れるとかそういうのは書いてません。
あと、結果をほぼ一致させたかったのでAS句の使えない（使えそうにない）MySQLの「SHOW TABLES」等も無しです。

テーブル一覧（コメント付）

Oracle

Oracle
SELECT
    TBL.TABLE_NAME  AS TABLE_NAME
,   CMM.COMMENTS    AS COMMENTS
FROM
    USER_TABLES TBL
    LEFT OUTER JOIN USER_TAB_COMMENTS CMM ON TBL.TABLE_NAME = CMM.TABLE_NAME
ORDER BY TBL.TABLE_NAME

PostgreSQL

PostgreSQL
SELECT
    TBL.TABLENAME                                           AS TABLE_NAME
,   OBJ_DESCRIPTION(TBL.TABLENAME::regclass, 'pg_class')    AS COMMENTS
FROM
    PG_CATALOG.PG_TABLES TBL
WHERE
    TBL.SCHEMANAME  = current_schema
ORDER BY
    TBL.TABLENAME

MySQL

MySQL
SELECT
    TBL.TABLE_NAME      AS TABLE_NAME
,   TBL.TABLE_COMMENT   AS COMMENTS
  FROM INFORMATION_SCHEMA.TABLES TBL
WHERE
    TBL.TABLE_SCHEMA    = DATABASE()
ORDER BY
    TBL.TABLE_NAME

SQLServer

SQLServer
SELECT
    CONVERT(VARCHAR, TBL.NAME)  AS TABLE_NAME
,   CONVERT(VARCHAR, CMM.VALUE) AS COMMENTS
FROM
    sys.tables              AS TBL
,   sys.extended_properties AS CMM
WHERE
    TBL.OBJECT_ID   = CMM.MAJOR_ID
AND CMM.MINOR_ID    = 0
ORDER BY
    TBL.NAME

H2Database

H2Database
SELECT
    TBL.TABLE_NAME  AS TABLE_NAME
,   TBL.REMARKS     AS COMMENTS
FROM
    INFORMATION_SCHEMA.TABLES   AS TBL
WHERE
    TBL.TABLE_SCHEMA    =       SCHEMA()
ORDER BY
    TBL.TABLE_NAME

プライマリキー一覧

Oracle

Oracle
SELECT
    CNS.TABLE_NAME      AS TABLE_NAME
,   CLM.CONSTRAINT_NAME AS CONSTRAINT_NAME
,   CLM.COLUMN_NAME     AS COLUMN_NAME
,   CLM.POSITION        AS POSITION
FROM
    USER_CONSTRAINTS CNS
    LEFT OUTER JOIN USER_CONS_COLUMNS CLM ON CNS.OWNER = CLM.OWNER AND CNS.CONSTRAINT_NAME = CLM.CONSTRAINT_NAME
WHERE
    CNS.OWNER           = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
AND CNS.CONSTRAINT_TYPE = 'P'
ORDER BY
    CNS.TABLE_NAME
,   CLM.POSITION

PostgreSQL

PostgreSQL
SELECT
    TC.TABLE_NAME           AS TABLE_NAME
,   TC.CONSTRAINT_NAME      AS CONSTRAINT_NAME
,   COL.COLUMN_NAME         AS COLUMN_NAME
,   COL.ORDINAL_POSITION    AS POSITION
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
        TC.TABLE_NAME       = CCU.TABLE_NAME
    AND TC.CONSTRAINT_NAME  = CCU.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE COL ON
        TC.TABLE_CATALOG    = COL.TABLE_CATALOG
    AND TC.TABLE_SCHEMA     = COL.TABLE_SCHEMA
    AND TC.TABLE_NAME       = COL.TABLE_NAME
    AND CCU.COLUMN_NAME     = COL.COLUMN_NAME
WHERE
    TC.CONSTRAINT_TYPE  = 'PRIMARY KEY'
ORDER BY
    TC.TABLE_NAME
,   COL.ORDINAL_POSITION

MySQL

MySQL
SELECT
    KCOL.TABLE_NAME         AS TABLE_NAME
,   KCOL.CONSTRAINT_NAME    AS CONSTRAINT_NAME
,   KCOL.COLUMN_NAME        AS COLUMN_NAME
,   KCOL.ORDINAL_POSITION   AS POSITION
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCOL
WHERE
    KCOL.TABLE_SCHEMA   = DATABASE()
ORDER BY
    KCOL.TABLE_NAME
,   KCOL.ORDINAL_POSITION

SQLServer

SQLServer
SELECT
    CONVERT(VARCHAR, TBL.NAME)      AS TABLE_NAME
,   CONVERT(VARCHAR, KCNST.NAME)    AS CONSTRAINT_NAME
,   CONVERT(VARCHAR, COLS.NAME)     AS COLUMN_NAME
,   IDXCOL.KEY_ORDINAL              AS POSITION
FROM
    sys.tables AS TBL
        INNER JOIN sys.key_constraints AS KCNST
        ON
            TBL.OBJECT_ID   = KCNST.PARENT_OBJECT_ID
        AND KCNST.TYPE  = 'PK'
        INNER JOIN sys.index_columns AS IDXCOL
        ON
            KCNST.PARENT_OBJECT_ID  = IDXCOL.OBJECT_ID
        AND KCNST.UNIQUE_INDEX_ID   = IDXCOL.INDEX_ID
        INNER JOIN sys.columns AS COLS
        ON
            IDXCOL.OBJECT_ID    = COLS.OBJECT_ID
        AND IDXCOL.COLUMN_ID    = COLS.COLUMN_ID
ORDER BY
    TBL.NAME
,   IDXCOL.KEY_ORDINAL

H2Database

H2Database
SELECT
    CON.TABLE_NAME          AS  TABLE_NAME
,   CON.CONSTRAINT_NAME     AS  CONSTRAINT_NAME
,   IDX.COLUMN_NAME         AS  COLUMN_NAME
,   IDX.ORDINAL_POSITION    AS  POSITION
FROM
    INFORMATION_SCHEMA.CONSTRAINTS  CON
,   INFORMATION_SCHEMA.INDEXES      IDX
WHERE
    CON.CONSTRAINT_SCHEMA   =   SCHEMA()
AND CON.TABLE_NAME          =   IDX.TABLE_NAME
AND CON.UNIQUE_INDEX_NAME   =   IDX.INDEX_NAME
AND IDX.TABLE_SCHEMA        =   CON.TABLE_SCHEMA
ORDER BY
    CON.TABLE_NAME
,   IDX.ORDINAL_POSITION

テーブル別カラム情報一覧（コメント付）

Oracle

Oracle
SELECT
    CLM.COLUMN_NAME     AS COLUMN_NAME
,   CMM.COMMENTS        AS COMMENTS
,   CLM.DATA_TYPE       AS DATA_TYPE
,   CLM.DATA_LENGTH     AS DATA_LENGTH
,   CLM.DATA_PRECISION  AS DATA_PRECISION
,   CLM.DATA_SCALE      AS DATA_SCALE
,   CLM.NULLABLE        AS NULLABLE
,   CLM.DATA_DEFAULT    AS DATA_DEFAULT
FROM
    USER_TAB_COLUMNS CLM
    LEFT OUTER JOIN USER_COL_COMMENTS CMM ON CLM.TABLE_NAME = CMM.TABLE_NAME AND CLM.COLUMN_NAME = CMM.COLUMN_NAME
WHERE
    CLM.TABLE_NAME = /* tableName */'TABLE_NAME'
ORDER BY
    CLM.TABLE_NAME
,   CLM.COLUMN_ID

PostgreSQL

PostgreSQL
SELECT
    COL.COLUMN_NAME                                                 AS COLUMN_NAME
,   COL_DESCRIPTION(COL.TABLE_NAME::regclass,COL.ORDINAL_POSITION)  AS COMMENTS
,   COL.DATA_TYPE                                                   AS DATA_TYPE
,   COL.CHARACTER_MAXIMUM_LENGTH                                    AS DATA_LENGTH
,   COALESCE(COL.NUMERIC_PRECISION,COL.DATETIME_PRECISION)          AS DATA_PRECISION
,   COL.NUMERIC_SCALE                                               AS DATA_SCALE
,   COL.IS_NULLABLE                                                 AS NULLABLE
,   COL.COLUMN_DEFAULT                                              AS DATA_DEFAULT
FROM
    INFORMATION_SCHEMA.COLUMNS COL
WHERE
    COL.TABLE_NAME  = /* tableName */'TABLE_NAME'
ORDER BY
    COL.TABLE_NAME
,   COL.ORDINAL_POSITION

MySQL

MySQL
SELECT
    COL.COLUMN_NAME                                         AS COLUMN_NAME
,   COL.COLUMN_COMMENT                                      AS COMMENTS
,   COL.DATA_TYPE                                           AS DATA_TYPE
,   COL.CHARACTER_MAXIMUM_LENGTH                            AS DATA_LENGTH
,   IFNULL(COL.NUMERIC_PRECISION,COL.DATETIME_PRECISION)    AS DATA_PRECISION
,   COL.NUMERIC_SCALE                                       AS DATA_SCALE
,   COL.IS_NULLABLE                                         AS NULLABLE
,   COL.COLUMN_DEFAULT                                      AS DATA_DEFAULT
FROM
    INFORMATION_SCHEMA.COLUMNS COL
WHERE
    COL.TABLE_SCHEMA    = DATABASE()
AND COL.TABLE_NAME      = /* tableName */'TABLE_NAME'
ORDER BY
    COL.TABLE_NAME
,   COL.ORDINAL_POSITION

SQLServer

SQLServer
SELECT
    CONVERT(VARCHAR, COL.NAME)  AS COLUMN_NAME
,   CONVERT(VARCHAR, EP.VALUE)  AS COMMENTS
,   ICOL.DATA_TYPE              AS DATA_TYPE
,   COL.MAX_LENGTH              AS DATA_LENGTH
,   COL.PRECISION               AS DATA_PRECISION
,   COL.SCALE                   AS DATA_SCALE
,   ICOL.IS_NULLABLE            AS NULLABLE
,   ICOL.COLUMN_DEFAULT         AS DATA_DEFAULT
FROM
    sys.tables                  AS TBL
,   sys.columns                 AS COL
,   sys.extended_properties     AS EP
,   INFORMATION_SCHEMA.COLUMNS  AS ICOL
WHERE
    TBL.NAME        = /* tableName */'TABLE_NAME'
AND TBL.OBJECT_ID   = COL.OBJECT_ID
AND COL.OBJECT_ID   = EP.MAJOR_ID
AND COL.COLUMN_ID   = EP.MINOR_ID
AND COL.NAME        = ICOL.COLUMN_NAME
AND TBL.NAME        = ICOL.TABLE_NAME
ORDER BY
    TBL.NAME
,   COL.COLUMN_ID

H2Database

H2Database
SELECT
    COL.COLUMN_NAME                 AS COLUMN_NAME
,   COL.REMARKS                     AS COMMENTS
,   COL.TYPE_NAME                   AS DATA_TYPE
,   COL.CHARACTER_MAXIMUM_LENGTH    AS DATA_LENGTH
,   COL.NUMERIC_PRECISION           AS DATA_PRECISION
,   COL.NUMERIC_SCALE               AS DATA_SCALE
,   COL.IS_NULLABLE                 AS NULLABLE
,   COL.COLUMN_DEFAULT              AS DATA_DEFAULT
FROM
    INFORMATION_SCHEMA.COLUMNS  COL
WHERE
    COL.TABLE_SCHEMA    = SCHEMA()
AND COL.TABLE_NAME      = /* tableName */'TABLE_NAME'
ORDER BY
    COL.TABLE_NAME
,   COL.ORDINAL_POSITION

テーブル別インデックス一覧

Oracle

Oracle
SELECT
    IDX.TABLE_NAME          AS TABLE_NAME
,   IDX.INDEX_NAME          AS INDEX_NAME
,   IDX.UNIQUENESS          AS UNIQUENESS
,   IDXCOL.COLUMN_NAME      AS COLUMN_NAME
,   IDXCOL.COLUMN_POSITION  AS POSITION
FROM
    USER_INDEXES IDX
,   USER_IND_COLUMNS IDXCOL
WHERE
    IDX.INDEX_NAME  = IDXCOL.INDEX_NAME
AND IDX.TABLE_NAME  = IDXCOL.TABLE_NAME
AND IDX.TABLE_NAME  = /* tableName */'TABLE_NAME'
ORDER BY
    IDX.TABLE_NAME
,   IDX.INDEX_NAME
,   IDXCOL.COLUMN_POSITION

PostgreSQL

PostgreSQL
SELECT
    IDXS.TABLENAME  AS TABLE_NAME
,   IDXS.INDEXNAME  AS INDEX_NAME
,   IDX.INDISUNIQUE AS UNIQUENESS
,   COL.COLUMN_NAME AS COLUMN_NAME
,   IDX.POSITION    AS POSITION
 FROM
    PG_INDEXES IDXS,
    (SELECT
        IDX.INDEXRELID                      AS INDEXRELID
    ,   IDX.INDISUNIQUE                     AS INDISUNIQUE
    ,   UNNEST(IDX.INDKEY)                  AS INDKEY
    ,   GENERATE_SUBSCRIPTS(IDX.INDKEY,1)   AS POSITION
    FROM
        PG_INDEX IDX
    ) IDX
    ,INFORMATION_SCHEMA.COLUMNS COL
WHERE
    IDXS.SCHEMANAME         = current_schema
AND IDXS.INDEXNAME          NOT LIKE '%pg_toast_%'
AND IDX.INDEXRELID          = IDXS.INDEXNAME::regclass
AND COL.TABLE_SCHEMA        = current_schema
AND COL.TABLE_NAME          = IDXS.TABLENAME
AND IDXS.TABLENAME          = /* tableName */'TABLE_NAME'
AND COL.ORDINAL_POSITION    = IDX.INDKEY
ORDER BY
    IDXS.TABLENAME
,   IDXS.INDEXNAME
,   IDX.POSITION

MySQL

MySQL
SELECT
    STT.TABLE_NAME                  AS TABLE_NAME
,   STT.INDEX_NAME                  AS INDEX_NAME
,   IF(STT.NON_UNIQUE=1,'NO','YES') AS UNIQUENESS
,   STT.COLUMN_NAME                 AS COLUMN_NAME
,   STT.SEQ_IN_INDEX                AS POSITION
FROM INFORMATION_SCHEMA.STATISTICS STT
WHERE
    STT.TABLE_SCHEMA    = DATABASE()
AND STT.TABLE_NAME      = /* tableName */'TABLE_NAME'
ORDER BY
    STT.TABLE_NAME
,   STT.INDEX_NAME
,   STT.SEQ_IN_INDEX

SQLServer

SQLServer
SELECT
    CONVERT(VARCHAR, TBL.NAME)      AS TABLE_NAME
,   CONVERT(VARCHAR, IDX.NAME)      AS INDEX_NAME
,   IDX.IS_UNIQUE                   AS UNIQUENESS
,   CONVERT(VARCHAR, COLS.NAME)     AS COLUMN_NAME
,   IDXCOL.KEY_ORDINAL              AS POSITION
FROM
    sys.tables AS TBL
        INNER JOIN sys.indexes AS IDX
        ON
            TBL.OBJECT_ID       = IDX.OBJECT_ID
        INNER JOIN sys.index_columns AS IDXCOL
        ON
            TBL.OBJECT_ID       = IDXCOL.OBJECT_ID
        AND IDX.INDEX_ID        = IDXCOL.INDEX_ID
        INNER JOIN sys.columns AS COLS
        ON
            IDXCOL.OBJECT_ID    = COLS.OBJECT_ID
        AND IDXCOL.COLUMN_ID    = COLS.COLUMN_ID
WHERE
    TBL.NAME    = /* tableName */'TABLE_NAME'
ORDER BY
    TBL.NAME
,   IDX.NAME
,   IDXCOL.KEY_ORDINAL

H2Database

H2Database
SELECT
    IDX.TABLE_NAME                                  AS  TABLE_NAME
,   COALESCE(CON.CONSTRAINT_NAME, IDX.INDEX_NAME)   AS  INDEX_NAME
,   CASE
        WHEN
            IDX.NON_UNIQUE
        THEN
            'no'
        ELSE
            'yes'
    END                                             AS  UNIQUENESS
,   IDX.COLUMN_NAME                                 AS  COLUMN_NAME
,   IDX.ORDINAL_POSITION                            AS  POSITION
FROM
    INFORMATION_SCHEMA.INDEXES  IDX
LEFT JOIN
    INFORMATION_SCHEMA.CONSTRAINTS  CON ON
    (
        CON.TABLE_NAME          =   IDX.TABLE_NAME
    AND CON.UNIQUE_INDEX_NAME   =   IDX.INDEX_NAME
    AND IDX.TABLE_SCHEMA        =   CON.TABLE_SCHEMA
    )
WHERE
    IDX.TABLE_SCHEMA    =   SCHEMA()
AND IDX.TABLE_NAME      =   /* tableName */'TABLE_NAME'
ORDER BY
    IDX.TABLE_NAME
,   IDX.INDEX_NAME
,   IDX.ORDINAL_POSITION

あとがき

JOINのやりかたがそれぞれ自由で醜いのはごめんなさい。
どこかで参考にしたSQLをなるべく崩さず書いていたりするためです。

次は、JDBC経由での実行計画の取り方について書いています。
Oracle・PostgreSQL・MySQL・SQLServer・H2DatabseそれぞれでJDBCを利用して実行計画を取得する

参考にしたサイト一部

ありがとうございます助かりました
PostgreSQL参考
https://www.postgresql.jp/document/9.4/html/functions-info.html
http://devlights.hatenablog.com/entry/20080226/p1
http://landhere.info/blog/a65.html
http://ameblo.jp/blueskyame/entry-10261155548.html
http://chopl.in/post/2013/11/07/how_to_retrieve_tables_and_columns_with_postgres/
http://d.hatena.ne.jp/ja9/20100830/1283146058
SQLServer参考
http://lightgauge.net/database/sqlserver/3010/
http://lightgauge.net/database/sqlserver/3697/
http://lightgauge.net/database/sqlserver/3682/
MySQL参考
http://d.hatena.ne.jp/tanamon/20090325/1237972922
http://hack.aipo.com/archives/3513/
H2参考
http://www.h2database.com/html/grammar.html#information_schema
http://groovyarekore.blogspot.jp/2010/09/groovyh2-database_22.html

53
54
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
53
54