Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 ;
- <?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();
- ?>
- import MySQLdb
- conn = MySQLdb.connect(
- host='localhost',
- user='root',
- passwd='mypassword',
- db='mydatabase'
- )
- c = conn.cursor()
- c.execute('SHOW TABLES')
- for table in c.fetchall():
- c.execute('SELECT * FROM %s' % table[0])
- if c.rowcount == 0:
- c.execute('DROP TABLE %s' % table[0])
Add Comment
Please, Sign In to add comment