MySQL数据库中的非空表列表

Can I get MySQL to return all non-empty tables in a database? Much like "SHOW TABLES" but only those that are not empty.

我能让MySQL返回数据库中的所有非空表吗?很像“显示表”，但只显示那些不是空的表。

4 个解决方案

#1

'information_schema' should be holding the relevant details. You can try

“information_schema”应该包含相关的细节。你可以试着

SELECT table_type,
       table_name
FROM information_schema.tables
WHERE table_rows >= 1;

to select from a selective database. You can also filter by TABLE_SCHEMA:

从选择的数据库中选择。还可以通过TABLE_SCHEMA进行筛选:

SELECT table_schema,
       table_type,
       table_name 
FROM information_schema.tables
WHERE table_rows >= 1
  AND TABLE_SCHEMA=?

#2

Use database 'information_schema' and run

使用数据库“information_schema”并运行

SELECT * FROM `TABLES` WHERE `TABLE_ROWS` > 0

this will give you all non-empty tables in the server for a certain database run

这将使您在服务器中为某个数据库运行提供所有非空的表。

SELECT * FROM `TABLES` WHERE `TABLE_ROWS` > 0  AND `TABLE_SCHEMA` = 'database_name'

#3

The accepted answer never worked for me, information_schema table_rows have some very weird values.

接受的答案对我来说并不适用，information_schema table_rows有一些非常奇怪的值。

This worked like a charm:

这很有魅力:

SHOW TABLE STATUS WHERE Rows > 0;

Docs for SHOW TABLE STATUS

显示表状态的文档

#4

You can run this query via phpMyAdmin:

你可以通过phmypadmin运行这个查询:

SELECT * 
FROM  `information_schema`.`TABLES` 
WHERE  `TABLE_ROWS` > 0

will return a list of non-empty tables

会返回一个非空表列表吗

#1