db object list

MariaDB, MySQL 내 db, table, column, index, constraints를 조회하는 query 이다. 

-- db
SELECT db.schema_name
     , db.default_character_set_name
     , db.default_collation_name
     , COUNT(tbl.table_name) AS tbl_cnt
  FROM information_schema.schemata AS db 
       LEFT OUTER JOIN information_schema.tables AS tbl 
                    ON db.schema_name = tbl.table_schema
 WHERE db.schema_name NOT IN ('information_schema', 'mysql', 'performance_schema')
 GROUP BY 1
 ORDER BY 4 DESC, 1
;


-- tbl
SELECT tbl.table_schema
     , tbl.table_name
     , tbl.table_comment
     , tbl.table_type
     , tbl.engine
     , tbl.row_format
     , tbl.table_collation
     , t1.partiton_YN
     , t2.constraint_cnt
     , t3.index_cnt
     , t4.column_cnt
  FROM information_schema.tables tbl
       LEFT OUTER JOIN (SELECT table_schema, table_name, IF(partition_name IS NULL, 'N', 'Y') AS partiton_YN 
                          FROM information_schema.partitions ) AS t1
                    ON tbl.table_schema = t1.table_schema
                   AND tbl.table_name = t1.table_name  
       LEFT OUTER JOIN (SELECT table_schema, table_name, COUNT(constraint_name) AS constraint_cnt 
                          FROM information_schema.table_constraints 
                         GROUP BY 1,2) AS t2
                    ON tbl.table_schema = t2.table_schema
                   AND tbl.table_name = t2.table_name
       LEFT OUTER JOIN (SELECT table_schema, table_name, COUNT(index_name) AS index_cnt
                          FROM information_schema.statistics 
                         GROUP BY 1,2) AS t3
                    ON tbl.table_schema = t3.table_schema
                   AND tbl.table_name = t3.table_name                        
       LEFT OUTER JOIN (SELECT table_schema, table_name, COUNT(column_name) AS column_cnt 
                          FROM information_schema.columns
                         GROUP BY 1,2) AS t4
                    ON tbl.table_schema = t4.table_schema
                   AND tbl.table_name = t4.table_name
 WHERE tbl.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
 GROUP BY 1,2
 ORDER BY 1,2
;


-- column
SELECT table_schema
     , table_name
     , ordinal_position
     , column_name
     , column_comment
     , column_key
     , column_type
     , is_nullable
     , column_default
     , character_set_name
     , collation_name
     , privileges
  FROM information_schema.columns
 ORDER BY 1,2,3
;


-- index
SELECT table_schema
     , table_name
     , non_unique
     , index_name
     , seq_in_index
     , column_name
     , index_type
     , comment
     , index_comment
  FROM information_schema.statistics
 ORDER BY 1,2,5
;


-- constraints 
SELECT table_schema
     , table_name
     , constraint_name
     , constraint_type
  FROM information_schema.table_constraints
 ORDER BY 1,2
;