Hi

I'm just wondering if there is a MySQL command that I can use (maybe in a php script / command in phpMyAdmin) to find any table that is empty?

All the tables have a prefix say "Test_TableName" and it is these tables with the prefix that I need to delete.

I don't know the name of the tables as they are made on the fly and there are 1000's of them hence why a script would probably be better ?

Spent a good while on google / search but nothing seems to pop up about finding tables that are empty and you have no idea what they are called!

Any pointers would be much appreciated 😃

    Hi

    Empty to me means the table has been made 'ready' for data, but it contains zero rows as yet.

    🙂

      TABLE_ROWS = 0 should be all you need then

        Hey dude Many thanks for this, quite simply brill 😃

        Complete MySQL Query for finding any empty table with a prefix_

        SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS = 0 AND TABLE_SCHEMA = 'your_db_name'
AND table_name LIKE '%Prefix_%'

        🙂

          you don't need the first % in the table_name like if the prefix is well a prefix, as there shouldn't be anything before it.

            Ah, Cool, yes that works too, just trying to find out a quick DELETE/DROP command but it doesn't seem to work, or does that bit require a script to loop thro the results and drop each table in turn?

              you can't delete from TABLE_SCHEMA, you will have to loop through the results of your select to drop the table(s)

                Write a Reply...