Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    1

    Red face Unanswered: Drop all tables that are empty in db

    Hi, i have 650tables, i am trying to drop empty tables in a db here in phpmyadmin. Please help, anyone willing to help and share?

    Really appreciate it,
    Albert

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    1. Script out the database (schema and data)
    2. Use AWK, Perl, etc. to filter the CREATE TABLE for empty tables
    3. Drop the old database
    4. Rebuild from the script
    This is a bit drastic, but it gives you many safety features including a documented fall-back position when you discover the error of your ways!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    10
    1.Make a JDBC Connection from java to your database
    2.With SELECT COUNT(*) FROM YOUR.TABLE you get the number of rows.
    3.look after row count in for each-loop and genereate DROP TABLE YOUR.TABLE where the rowcount is 0

  4. #4
    Join Date
    Mar 2013
    Posts
    6
    for one table you can use:

    DROP TABLE table_name ;

    for many tables (the bold are the table names):

    DROP TABLE table_a, table_b, table_c;
    Last edited by hujan; 03-21-13 at 06:27.

  5. #5
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Hi

    Try this but be carefull using it.


    Code:
    delimiter $$
    create procedure drop_tables_row0( schem varchar(255))
    begin
     select @strn:=concat('drop table ', group_concat(table_name))
     from information_schema.tables
     where table_schema=schem and table_rows =0;
    
     prepare query_stm from @strn;
     execute query_stm;
     drop prepare query_stm;
    end$$
    
    
    call drop_tables_row0('your_db')$$
    Thanks
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •