Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List of non-empty tables in MySQL database

Tags:

sql

mysql

People also ask

How do I find non empty tables in database?

mysql> select table_type, table_name from information_schema. tables −> where table_rows >= 1; Above, we have considered only the table that have 1 or more than 1 rows i.e. non-empty table.

How do I get a list of tables in MySQL?

To get a list of the tables in a MySQL database, use the mysql client tool to connect to the MySQL server and run the SHOW TABLES command. The optional FULL modifier will show the table type as a second output column.

How check if table is empty MySQL?

SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR yourSpecificColumnName = ' '; The IS NULL constraint can be used whenever the column is empty and the symbol ( ' ') is used when there is empty value.


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

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:

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

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

This worked like a charm:

SHOW TABLE STATUS WHERE Rows > 0;

Docs for SHOW TABLE STATUS


Use database 'information_schema' and run

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'