Muchas veces MySQL te puede sorprender con la información que almacena y que en muchos casos no sabes de que dispones. A continuación os dejo algunas de esas consultas que pueden resultar de interés para alguno, espero.
Listado de tablas con más de N número de filas
mysql> SELECT table_name as db, table_rows -> FROM information_schema.tables -> WHERE table_rows > 1000 -> AND table_schema not in('information_schema','performance_schema') -> ORDER BY table_rows desc; +---------------------------+------------+ | db | table_rows | +---------------------------+------------+ | zabbix.history | 127073544 | | zabbix.history_uint | 58873482 | | zabbix.trends_uint | 1619018 | | zabbix.trends | 1310026 | | zabbix.history_str | 340954 | | zabbix.housekeeper | 50843 | | zabbix.alerts | 37877 | | zabbix.events | 32635 | | zabbix.history_log | 25757 | | zabbix.items_applications | 1496 | | zabbix.items | 1014 | +---------------------------+------------+ 11 rows in set (1.67 sec)
Listado de accesos a una base de datos
mysql> SELECT grantee, privilege_type, is_grantable -> FROM information_schema.schema_privileges -> WHERE table_schema = 'zabbix'; +----------------------+----------------+--------------+ | grantee | privilege_type | is_grantable | +----------------------+----------------+--------------+ | 'zabbix'@'localhost' | SELECT | NO | | 'zabbix'@'localhost' | INSERT | NO | | 'zabbix'@'localhost' | UPDATE | NO | | 'zabbix'@'localhost' | DELETE | NO | | 'zabbix'@'localhost' | CREATE | NO | | 'zabbix'@'localhost' | DROP | NO | | 'zabbix'@'localhost' | REFERENCES | NO | | 'zabbix'@'localhost' | INDEX | NO | | 'zabbix'@'localhost' | ALTER | NO | | 'zabbix'@'localhost' | LOCK TABLES | NO | | 'zabbix'@'localhost' | EXECUTE | NO | | 'zabbix'@'localhost' | CREATE VIEW | NO | | 'zabbix'@'localhost' | SHOW VIEW | NO | | 'zabbix'@'localhost' | CREATE ROUTINE | NO | | 'zabbix'@'localhost' | ALTER ROUTINE | NO | | 'zabbix'@'localhost' | EVENT | NO | | 'zabbix'@'localhost' | TRIGGER | NO | +----------------------+----------------+--------------+ 18 rows in set (0.00 sec)
Listado de base de datos + tabla que no tienen primary key
mysql> SELECT CONCAT(t.table_schema,'.',t.table_name) as table_name -> FROM information_schema.TABLES t -> LEFT JOIN information_schema.TABLE_CONSTRAINTS tc -> ON t.table_schema = tc.table_schema -> AND t.table_name = tc.table_name -> AND tc.constraint_type = 'PRIMARY KEY' -> WHERE tc.constraint_name IS NULL -> AND t.table_type = 'BASE TABLE' -> AND t.table_schema not in('information_schema','performance_schema'); +---------------------+ | table_name | +---------------------+ | zabbix.events | | zabbix.history | | zabbix.history_str | | zabbix.history_uint | | zabbix.node_cksum | +---------------------+ 5 rows in set (1.66 sec)
No hay comentarios :
Publicar un comentario