How to delete all tables that have 1-2 rows

Question

Im having a database which is over 60k tables and i want to delete all the tables that have 1 or 2 rows.

Answer 1

You can use the below code in SQL Server 2012. It will delete all the tables which is having row_count less than 3.

USE [YourDB]
GO
DECLARE @Max int, @Count int,@Table_Name Varchar(20)
SET @Max =0
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp 
    (
    table_name sysname ,
    row_count INT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50)
    )
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
DROP TABLE #temp1
CREATE TABLE #temp1
    (
    ID int IDENTITY(1,1),
    table_name sysname ,
    row_count INT
    )
SET NOCOUNT ON
    INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    INSERT INTO #temp1
        SELECT a.table_name,
        a.row_count
        FROM #temp a
        INNER JOIN information_schema.columns b
        ON a.table_name collate database_default
        = b.table_name collate database_default
        GROUP BY a.table_name, a.row_count
        HAVING a.row_count <3


    SET @Count =(SELECT COUNT(*) FROM #temp1)

    WHILE @Count > @Max
    BEGIN
        SET @Max = @Max +1
        SET @Table_Name = (SELECT table_name FROM #temp1 WHERE ID = @Max)
        EXEC('DROP TABLE ' +@Table_Name)
    END

Answer 2

• Use a MySQL-GUI, order by number of rows and drop all tables with 1-2 rows. it is as easy as deleting files in a windows folder. this would take ~10 seconds + sort and drop time

or

• Select table names of tables with 1-2 rows from information_schemas, load into an excel file and build your drop statements. takes around 2-5 minutes + drop time

or

• Build a stored procedure that uses a Cursor to parse all the relevant table names into variables of your drop statement (like Hansa mentioned). this makes sense if you want to repeat your process from time to time. takes around 1-12 hours for beginners (depending on knowledge level) + drop time

Since logging in on SO probably took more time than solution 1 would, i would recommend that solution.

In case you want to spend more time , the following query will show all table names for tables with 1-2 rows:

SELECT table_schema, table_name From information_schema.tables
WHERE table_schema='your_schema' # use your table_schema here
AND table_rows BETWEEN 1 and 2 ;

Answer 3

I would do it within some application side logic, using a programming language of your choice (which offers a mysql API).

1. Get all table names, described here, grouping them by table names e.g. this could look something like:

   SELECT COUNT(table_rows), table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{your_db}' GROUP BY table_name;

(this is not tested, but to give you a basic idea...)

3. Execute a delete statement for the tables with rowcount 2 or lower. Should be an easy task as soon as you got the results in your programming language (btw SQL statement to delete them is "DROP TABLE")

Note: I think it should be also possible using a SQL cursor, but as I said I would prefer the above solution.

Answer 4

You can try this. First take all the table names from the database. After getting all the table names put them in a array and execute a foreach loop that checks the num of rows for each table, something like this

$tables = array();

foreach ($tables as $table_name){
    $query    = "select * from '$table_name'";
    $result   = mysqli_query($dbCon, $query);
    $num_rows = mysql_num_rows($result);

    if($num_rows < 3){
        $delete = "drop table $table_name";
        $res_del = mysqli_query($dbCon, $delete);
        echo $table_name." Deleted";
    }
}

Answer 5

You probably cant do this with 1 SQL Statement unless you are using stored procedures (see below). You will need to select all tables in MySQL with a programming language (e.g. Java with JDBC) using the following statement:

select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_ROWS <= 2 AND TABLE_ROWS >= 1

Then you can use the results in the programming languge to issue drop Statements for each result record in a loop:

drop table <tablename>

For doing this with a stored procedure (i.e. without a programming language) see the third comment on this page: MySQL Reference Drop Table. Of course you will need to adapt this to your needs because this example does not select tables by their row numbers but by their names.