ver - mostrar tablas de una base de datos mysql en php

Mostrar tablas por motor en MySQL

Question

¿Cómo mostraría todas las tablas en MySQL con un motor dado, por ejemplo, InnoDB, MyISAM, FEDERATED?

Answer 1

Otros ejemplos aquí.

Todas las tablas por motor (excepto las tablas del sistema):

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine
FROM information_schema.TABLES
WHERE ENGINE = ''MyISAM'' -- or InnoDB or whatever
AND TABLE_SCHEMA NOT IN(''mysql'',''information_schema'',''performance_schema'');

Todas las tablas excepto el motor (excepto las tablas del sistema):

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine
FROM information_schema.TABLES
WHERE ENGINE != ''MyISAM'' -- or InnoDB or whatever
AND TABLE_SCHEMA NOT IN(''mysql'',''information_schema'',''performance_schema'');

Answer 2

Si alguno tiene un problema y quiere ver en qué DB están las tablas con motor específico

SELECT
(SELECT group_concat(TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ''database1''
AND engine = ''MyIsam''
) as database1,
(SELECT group_concat(TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ''database2''
AND engine = ''MyIsam''
) as database2,
(SELECT group_concat(TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ''database3''
AND engine = ''MyIsam''
) as database3;

Saludos.

Answer 3

Si quieres los resultados de una base de datos única

SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ''dbname'' AND engine = ''InnoDB'';

Answer 4

Utilice la tabla INFORMATION_SCHEMA.TABLES :

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE engine = ''InnoDB''