yYMzCHqb

1DELIMITER $$
2
3DROP PROCEDURE IF EXISTS `drop_empty_tables_from` $$
4
5CREATE PROCEDURE `drop_empty_tables_from`(IN schema_target VARCHAR(128))
6BEGIN
7    DECLARE table_list TEXT;
8    DECLARE total      VARCHAR(11);
9
10    SELECT
11        GROUP_CONCAT(`TABLE_NAME`),
12        COUNT(`TABLE_NAME`)
13    INTO
14        table_list,
15        total
16    FROM `information_schema`.`TABLES`
17    WHERE
18          `TABLE_SCHEMA` = schema_target
19      AND `TABLE_ROWS`   = 0;
20
21    IF table_list IS NOT NULL THEN
22        SET @drop_tables = CONCAT("DROP TABLE ", table_list);
23
24        PREPARE stmt FROM @drop_tables;
25        EXECUTE stmt;
26        DEALLOCATE PREPARE stmt;
27    END IF;
28
29    SELECT total AS affected_tables;
30END $$
31
32DELIMITER ;
33	
34<?php
35$username="root";
36$password="mypassword";
37$database="mydatabase";
38mysql_connect('localhost',$username,$password);
39mysql_select_db($database) or die( "Unable to select database");
40
41function drop_empty_tables(){
42    $tables = mysql_query('SHOW TABLES');
43    while($table = mysql_fetch_array($tables)){
44        $table = $table[0];
45        $records = mysql_query("SELECT * FROM $table");
46        if(mysql_num_rows($records) == 0){
47            // mysql_query("DROP TABLE $table");
48            echo "DROP TABLE $table;n";
49        }
50    }
51}
52
53drop_empty_tables();
54?>
55	
56import MySQLdb
57
58conn = MySQLdb.connect(
59    host='localhost',
60    user='root',
61    passwd='mypassword',
62    db='mydatabase'
63    )
64
65c = conn.cursor()
66c.execute('SHOW TABLES')
67for table in c.fetchall():    
68    c.execute('SELECT * FROM %s' % table[0])
69    if c.rowcount == 0:
70        c.execute('DROP TABLE %s' % table[0])