Some useful SQL

Hopefully, someone will find this helpful.
I’ve just started working on a customer’s database with nearly 1000 tables and views. A lot of them are empty.
It was going to take far too long to sort through all the rubbish in the UI, so I looked at how Metabase stores the data.

First, I had to check that all the tables had finished having their initial scan:
SELECT count(*) FROM metabase.metabase_table where rows is null;
That returns the number of tables still to be scanned.

Then a quick check on how many tables have 0 records:
select * from metabase.metabase_table where rows = 0

Finally, hide the rubbish. This sets all tables with no rows to be hidden.:
update dalecaredashboard.metabase_table set visibility_type='cruft' where rows = 0
You may need to disable safe mode in MySQL workbench for that.

I had 427 tables/views without any records. Much easier to manage now.

9 Likes