インデックス取得SQL

インデックス情報を取得するSQL文です。
※DBによってはテーブルId.が大文字だったり小文字だったりします

MySQL ( MariaDB )

select INDEX_NAME
      ,SEQ_IN_INDEX
      ,COLUMN_NAME
      ,NON_UNIQUE
  from INFORMATION_SCHEMA.STATISTICS T1
 where INDEX_NAME <> 'PRIMARY'
   and TABLE_SCHEMA = '【DB Id.】'
   and TABLE_NAME = '【テーブルId.】'
   and NOT EXISTS (select 'X'
                     from INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
                    where ID = CONCAT('【DB Id.】/',T1.INDEX_NAME)
                  )
 order by INDEX_NAME
         ,SEQ_IN_INDEX
-- ※ver.8.0以上の場合は上記SQLに以下修正をしてください
--   INNODB_SYS_FOREIGN → INNODB_FOREIGN

PostgreSQL

select t1.indexname
      ,t1.indexdef
  from pg_indexes t1
       left join information_schema.table_constraints t2
    on t1.tablename = t2.table_name
   and t1.indexname = t2.constraint_name
   and t2.constraint_type = 'PRIMARY KEY'
 where t2.table_name is null
   and t1.tablename = '【テーブルId.】'
 order by t1.indexname

Oracle

SELECT IND.INDEX_NAME
      ,COL.COLUMN_POSITION
      ,COL.COLUMN_NAME
  FROM USER_INDEXES IND
       INNER JOIN USER_IND_COLUMNS COL
    ON IND.INDEX_NAME = COL.INDEX_NAME
 WHERE IND.TABLE_OWNER = '【スキーマ(ユーザー)Id.】'
   AND IND.TABLE_NAME = '【テーブルId.】'
   AND NOT EXISTS ( SELECT 'X' FROM USER_CONSTRAINTS
                     WHERE OWNER = IND.TABLE_OWNER
                       AND TABLE_NAME = IND.TABLE_NAME
                       AND CONSTRAINT_NAME = IND.INDEX_NAME
                       AND CONSTRAINT_TYPE = 'P'
                  )
 ORDER BY IND.INDEX_NAME
         ,COL.COLUMN_POSITION

SQL Server

select t1.name as index_name
     , t2.key_ordinal
     , t4.name
     , t1.is_unique
  from sys.indexes t1
       inner join sys.index_columns t2
    on t1.object_id = t2.object_id
   and t1.index_id = t2.index_id
   and t1.is_primary_key <> 1
       inner join sys.tables t3
    on t2.object_id = t3.object_id
       inner join sys.columns t4
    on t2.column_id = t4.column_id
   and t2.object_id = t4.object_id
 where t3.name = '【テーブルId.】'
 order by t1.index_id
     , t2.key_ordinal