query-estatisticas.sql

-- Query estatísticas

SET @nome_do_schema = 'db_name';
Select
(select schema_name from information_schema.schemata where schema_name=@nome_do_schema) as "Nome do Banco de dados",
(SELECT Round( Sum( data_length + index_length ) / 1024 / 1024, 3 )
FROM information_schema.tables
WHERE table_schema=@nome_do_schema
GROUP BY table_schema) as "Tamanho do Banco de dados em Mega Bytes",
(select count(*) from information_schema.tables where table_schema=@nome_do_schema and table_type='base table') as "Quant. Tabelas",
(select count(*) from information_schema.statistics where table_schema=@nome_do_schema) as "Quant. Índices",
(select count(*) from information_schema.views where table_schema=@nome_do_schema) as "Quant. Views",
(select count(*) from information_schema.routines where routine_type ='FUNCTION' and routine_schema=@nome_do_schema) as "Quant. Funções",
(select COUNT(*) from information_schema.routines where routine_type ='PROCEDURE' and routine_schema=@nome_do_schema) as "Quant. Procedimentos",
(select count(*) from information_schema.triggers where trigger_schema=@nome_do_schema) as "Quant. Triggers",
(select default_collation_name from information_schema.schemata where schema_name=@nome_do_schema)"Default collation do Banco de dados",
(select default_character_set_name from information_schema.schemata where schema_name=@nome_do_schema)"Default charset do Banco de dados",
(select sum((select count(*) from information_schema.tables where table_schema=@nome_do_schema and table_type='base table')+(select count(*) from information_schema.statistics where table_schema=@nome_do_schema)+(select count(*) from information_schema.views where table_schema=@nome_do_schema)+(select count(*) from information_schema.routines where routine_type ='FUNCTION' and routine_schema=@nome_do_schema)+(select COUNT(*) from information_schema.routines where routine_type ='PROCEDURE' and routine_schema=@nome_do_schema)+(select count(*) from information_schema.triggers where trigger_schema=@nome_do_schema))) as "Total de Objetos do Banco de dados"
LIMIT 0, 1000\G


select a.TABLE_SCHEMA, a.TABLE_TYPE , a.TABLE_NAME, a.TABLE_ROWS from information_schema.tables a where a.table_schema='db_name';