Below are some of the common scenarios where information_schema is useful :
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 :
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'), …[Read more]
