MySQL Index 정보 조회 (key column 을 한줄로 표시)

Index 정보를 조회하여 하나의 컬럼에 Key 컬럼을 ','로 구분하여 출력하는 쿼리 입니다.

 

실행 결과)

쿼리)

select ta.table_name
    , index_name, max(tb.constraint_type) INDEX_DESCRIPTION  
    , concat_ws(', ', max(col_1), max(col_2), max(col_3), max(col_4), max(col_5)  
    , max(col_6), max(col_7), max(col_8), max(col_9), max(col_10)) INDEX_KEYS  
from (  
        select table_schema, `table_name`  
        , (CONSTRAINT_NAME) INDEX_NAME  
        , ('') INDEX_DESCRIPTION  
        , (case ordinal_position when 1 then column_name else null end) col_1  
        , (case ordinal_position when 2 then column_name else null end) col_2  
        , (case ordinal_position when 3 then column_name else null end) col_3  
        , (case ordinal_position when 4 then column_name else null end) col_4  
        , (case ordinal_position when 5 then column_name else null end) col_5  
        , (case ordinal_position when 6 then column_name else null end) col_6  
        , (case ordinal_position when 7 then column_name else null end) col_7  
        , (case ordinal_position when 8 then column_name else null end) col_8  
        , (case ordinal_position when 9 then column_name else null end) col_9  
        , (case ordinal_position when 10 then column_name else null end) col_10  
        from information_schema.KEY_COLUMN_USAGE  
        where table_schema = 'db_schema' and `table_name` like '%info%'  
    ) ta  
left join information_schema.table_constraints tb   
on ta.table_schema = tb.table_schema and ta.`table_name` = tb.`table_name` and ta.index_name = tb.constraint_name  
group by ta.table_name, index_name 
order by ta.table_name, index_name asc
;