CODEWITHSUNDEEP
mysqldatabase
UgoL
UgoL

Reputation: 919

Show only the filled tables' list on a MySQL database schema

I'm searching a way to show only the filled tables' list on a MySQL database schema, in other words by filtering out all the empty tables.

I know that I can perform something like that for show all the tables within a database schema:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'myd_db_schema_name'

What if I want to add an additional WHERE condition for show only NOT NULL tables's list?

Upvotes: 0

Views: 207

Answers (2)

UgoL
UgoL

Reputation: 919

Okay, this is the solution:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'my_db_schema_name' AND table_rows >= 1

Instead of using COLUMNS I used TABLES.

Upvotes: 0

Malte Kölle
Malte Kölle

Reputation: 192

I don't know if it helps but you can use two WHERE Clause with AND

And to filter out the empty Tables you can check if the Table has a row like this: 

WHERE table_rows >= 1

So the full Query would be

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'myd_db_schema_name' AND table_rows >= 1

I haven't tried it out but hopefully it works.

Upvotes: 1

Related Questions