CODEWITHSUNDEEP
mysql
fmalina
fmalina

Reputation: 6310

How do I drop all empty tables from a MySQL database?

How do I drop all empty tables from a MySQL database, leaving only the tables that have at least 1 record?

EDIT: This is what I did using Python 3 with mysqlclient

import MySQLdb

conn = MySQLdb.connect(
    host='localhost', user='root',
    passwd='mypassword', db='mydatabase'
)

c = conn.cursor()
c.execute('SHOW TABLES')
for row in c.fetchall():
    table_name = row[0]
    c.execute(f'SELECT * FROM {table_name}')
    if c.rowcount == 0:
        # c.execute(f'DROP TABLE {table_name}')
        print(f'DROP TABLE {table_name}')

Upvotes: 10

Views: 8356

Answers (8)

mamal
mamal

Reputation: 1986

try this :

  $tables = $conn->query('SHOW TABLES');
            $i = 0;
                while($table = $tables->fetch_array(MYSQLI_NUM)){
                    $table = $table[0];
                    $records =  $conn->query("SELECT * FROM `$table`");
                    if($records->num_rows == 0){
                        if ($conn->query(" DROP TABLE `$table` ")){
                           $i++;
                        }
                    }
                }
       return $i;

variable $i show count of table deleted

Upvotes: 0

Eat Ong
Eat Ong

Reputation: 537

Another way using PHP is:

#connect to database first

$sql = "SHOW TABLE STATUS FROM `".$database."` 
WHERE ENGINE IS NOT NULL
AND `Rows`=0";
if($res = mysql_query($sql)){
    if(mysql_numrows($res)){
        while($r = mysql_fetch_assoc($res)){
            $table = $r['Name'];
            echo '<br />'.$table;
            if($ret = mysql_query("DROP TABLE `".$table."`")){
                echo ' = DROPPED';
            }else{
                echo ' = '.mysql_error();
            }
        }
    }
}

This removes another execution just to determine that the table has no rows.

I'm getting problems with the GROUP_CONCAT using the stored procedure. What's strange is that some tables are not recognized of they are not found it seems.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562330

Use pt-find from Percona Toolkit:

$ pt-find --dblike "mydatabase" --empty --exec-plus "DROP TABLE %s"

Upvotes: 5

AskApache Htaccess
AskApache Htaccess

Reputation: 1110

If used straight from the shell, replace db with database_name. Note this is live, no uncommenting needed.

db='wordpress_db';
for f in $(mysql -e "use $db;SHOW TABLES;" | sed 's/^| \([^ ]*\).*$/\1/g'|sed 1d);
do 
   c=`mysql -e "use $db;SELECT COUNT(*) FROM ${f}\G"|sed '/:/!d; s/^[^:]*: //g'`;
   [[ $c == 0 ]] && { echo "DROP db.$f" && mysql -e "use $db;DROP TABLE $f"; }
done

Upvotes: 1

DomeDan
DomeDan

Reputation: 11

Here is a modified version of the php-script posted earlier,
I added a function that loop through all databases (and as the original script, uncomment mysql_query() when you want to run the script for real)

<?php
$username="root";
$password="mypassword";
$database="information_schema";
mysql_connect('localhost',$username,$password);
mysql_select_db($database) or die( "Unable to select database\n");

function find_databases() {
// edit the query if you want to add more databases that you dont want to mess with
$databaseq = mysql_query("SELECT DISTINCT(SCHEMA_NAME) FROM SCHEMATA WHERE SCHEMA_NAME not in ('information_schema','mysql','phpmyadmin');");
    while($databaser = mysql_fetch_array($databaseq)){
        echo "Checking $databaser[0] ...\n";
            drop_empty_tables_and_db($databaser[0]);
    }
}

function drop_empty_tables_and_db($get_database){
    mysql_select_db($get_database) or print( "Unable to select database $get_databas\n");
    $tables = mysql_query('SHOW TABLES');
    while($table = mysql_fetch_array($tables)){
        $table = $table[0];
        $records = mysql_query("SELECT * FROM $table");
        if(mysql_num_rows($records) == 0){
            echo "DROP TABLE `$table`; \n";
            //mysql_query("DROP TABLE `$table`") or print( "Unable to drop table $table\n");
        }
    }
    $tables = mysql_query('SHOW TABLES');
    if(mysql_num_rows($tables) == 0){
        echo "DROP DATABASE `$get_database`;\n";
        //mysql_query("DROP DATABASE `$get_database`") or print( "Unable to drop database $get_database\n");
    }
}

find_databases(); //...and remove them :)

?>

Upvotes: 1

fmalina
fmalina

Reputation: 6310

And a PHP version for completeness. It won't drop anything, just print for you the DROP statements:

<?php
$username="root";
$password="mypassword";
$database="mydatabase";
mysql_connect('localhost',$username,$password);
mysql_select_db($database) or die( "Unable to select database");

function drop_empty_tables(){
    $tables = mysql_query('SHOW TABLES');
    while($table = mysql_fetch_array($tables)){
        $table = $table[0];
        $records = mysql_query("SELECT * FROM $table");
        if(mysql_num_rows($records) == 0){
            // mysql_query("DROP TABLE $table");
            echo "DROP TABLE $table;\n";
        }
    }
}

drop_empty_tables();
?>

Upvotes: 3

Ionuț G. Stan
Ionuț G. Stan

Reputation: 179119

This stored procedure should do it:

DELIMITER $$

DROP PROCEDURE IF EXISTS `drop_empty_tables_from` $$

CREATE PROCEDURE `drop_empty_tables_from`(IN schema_target VARCHAR(128))
BEGIN
    DECLARE table_list TEXT;
    DECLARE total      VARCHAR(11);

    SELECT
        GROUP_CONCAT(`TABLE_NAME`),
        COUNT(`TABLE_NAME`)
    INTO
        table_list,
        total
    FROM `information_schema`.`TABLES`
    WHERE
          `TABLE_SCHEMA` = schema_target
      AND `TABLE_ROWS`   = 0;

    IF table_list IS NOT NULL THEN
        SET @drop_tables = CONCAT("DROP TABLE ", table_list);

        PREPARE stmt FROM @drop_tables;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

    SELECT total AS affected_tables;
END $$

DELIMITER ;

There may be problems with that GROUP_CONCAT when there are too many empty tables. It depends on the value of the group_concat_max_len system variable.

It's not possible to do it in one query because DROP TABLE cannot receive its arguments from a SELECT query.

InnoDB note

Thanks to James for his comments. It appears that the row count query won't return precise results in the case of InnoDB tables, so the above procedure is not guaranteed to work perfectly when there are InnoDB tables in that schema.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

Source: http://dev.mysql.com/doc/refman/5.1/en/tables-table.html

Upvotes: 4

Julius F
Julius F

Reputation: 3444

  • If "empty" means entries with "" IF (SELECT * FROM tablexy) DROP TABLE tablexy
  • If "empty" means not any entry IF (SELECT * FROM tablexy) DROP TABLE tablexy

(You have to run this queries for each table, because I did not found a way to perform one query on all tables in one query)

It is the same query, but I want to differ from NOTHING and SOMETHING WITH NO CONTENT ;)

Upvotes: 1

Related Questions