StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This stored procedure should do it:</p> <pre><code>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 ; </code></pre> <p>There may be problems with that <code>GROUP_CONCAT</code> when there are too many empty tables. It depends on the value of the <a href="http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_group_concat_max_len" rel="nofollow noreferrer"><code>group_concat_max_len</code></a> system variable.</p> <p>It's not possible to do it in one query because <code>DROP TABLE</code> cannot receive its arguments from a <code>SELECT</code> query.</p> <h3>InnoDB note</h3> <p>Thanks to <strong>James</strong> 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.</p> <blockquote> <p>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.)</p> </blockquote> <p>Source: <a href="http://dev.mysql.com/doc/refman/5.1/en/tables-table.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.1/en/tables-table.html</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload