Using information_schema tables – Part 1

by nivas on September 7, 2014

Below are some of the common scenarios where information_schema is useful :

Tables table

Tables table

How to check the database size using information_schema ?

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

How to list the tables created before 4 days ?

 select * from information_schema.tables where CREATE_TIME < NOW() - INTERVAL 4 DAY ;

Columns table :

columns table

List all the tables that have a column called birth_date

select table_name from information_schema.columns where table_schema='employees' and column_name='birth_date';

List all tables without primary/unique key:

select 
    table_schema,table_name 
from  
    information_schema.columns  
group by 
    table_schema,table_name   
having 
    sum(if(column_key in ('PRI','UNI'), 1,0)) = 0;

Statistics table :

statistics table

How to list all the indexes with column names in employees database ?

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'employees'
GROUP BY 1,2;

How to list all non-unique indexes in employees database ?

SELECT index_name,table_name from information_schema.statistics where table_schema='employees' and NON_UNIQUE=1 ;

How to check the cardinality of columns in a composite index emp_no ?

SELECT index_name,table_name,column_name,cardinality from information_schema.statistics where table_schema='employees' and index_name='emp_no';

Comments on this entry are closed.

Previous post:

Next post: