Show tables by engine in MySQL
How would I show all tables in MySQL with a given engine, e.g. Inno开发者_如何学PythonDB, MyISAM, FEDERATED?
Use INFORMATION_SCHEMA.TABLES
table:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'InnoDB'
If you want the results from a single database
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname' AND engine = 'InnoDB';
Other examples here.
All tables by engine (except system tables):
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');
All tables except engine (except system tables):
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');
If some has problem and want to see in which DB is tables with specific engine
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;
Regards.
精彩评论