Delete unused tables in Wordpress with a plugin

Get a cleaner en faster Wordpress site

Managing your WordPress database effectively is crucial to ensure your website remains optimized and runs smoothly. One of the challenges that many WordPress users face is handling unused database tables. These tables might be remnants from old plugins or themes that are no longer in use, or they might have been created by plugins that didn't clean up after themselves. Over time, these unused tables can accumulate, cluttering your database and causing potential performance issues.

In this blog post, we will explore a handy solution to this problem by creating a custom WordPress plugin to identify unused tables, list them, and provide options to download a backup and delete each table. We will walk you through the process of setting up the plugin, implementing the necessary functions, and adjusting the code to suit your specific WordPress installation.

By the end of this post, you will have a better understanding of how to manage your WordPress database more efficiently, freeing up space and maintaining optimal performance. Whether you are a WordPress developer, website administrator, or a casual blogger, this step-by-step guide will prove valuable in managing your site's database and ensuring it remains clutter-free.

Creating a WordPress plugin to identify unused tables, list them, and provide options to download a backup and delete the table is a multi-step process. Here's a script outline to help guide you through the process:

  1. Create a plugin file (e.g., wp-unused-table-manager.php) in the wp-content/plugins/ directory.

  2. Add the plugin header and necessary functions.

<?php
/*
Plugin Name: WP Unused Table Manager
Description: Identifies unused tables in the database, allows for download of backups, and provides a deletion option.
Version: 1.0
Author: Your Name
*/

// Register the admin menu
add_action('admin_menu', 'unused_table_manager_menu');

function unused_table_manager_menu() {
    add_management_page('Unused Table Manager', 'Unused Table Manager', 'manage_options', 'wp-unused-table-manager', 'unused_table_manager_page');
}

function unused_table_manager_page() {
    // Check user capabilities
    if (!current_user_can('manage_options')) {
        return;
    }

    // Perform actions based on $_POST variables
    if (isset($_POST['action'])) {
        // Add necessary actions here
    }

    // Display the plugin page
    ?>
    <div class="wrap">
        <h1>Unused Table Manager</h1>
        <?php unused_table_manager_list_tables(); ?>
    </div>
    <?php
}

function unused_table_manager_list_tables() {
    // Add necessary code (down below) to list tables
}

function unused_table_manager_backup_table($table_name) {
    // Add necessary code (down below) to backup table
}

function unused_table_manager_delete_table($table_name) {
    // Add necessary code (down below) to delete table
}
?>
  1. List the tables and check if they're used by WordPress or any active plugin.
function unused_table_manager_list_tables() {
    global $wpdb;
    $all_tables = $wpdb->get_col("SHOW TABLES");
    $unused_tables = array();

    // Add necessary code to filter unused tables

    // Display the unused tables
    echo '<table class="wp-list-table widefat fixed striped">';
    echo '<thead><tr><th>Table Name</th><th>Actions</th></tr></thead><tbody>';

    foreach ($unused_tables as $table) {
        echo '<tr><td>' . $table . '</td><td><a href="?page=wp-unused-table-manager&action=backup&table=' . $table . '">Download Backup</a> | <a href="?page=wp-unused-table-manager&action=delete&table=' . $table . '" onclick="return confirm(\'Are you sure you want to delete this table?\')">Delete</a></td></tr>';
    }

    echo '</tbody></table>';
}
  1. Implement the functions for backing up and deleting tables.
function unused_table_manager_backup_table($table_name) {
    global $wpdb;

    // Get table structure
    $table_structure = $wpdb->get_row("SHOW CREATE TABLE $table_name", ARRAY_N);
    $create_table_sql = $table_structure[1] . ";\n\n";

    // Get table data
    $table_data = $wpdb->get_results("SELECT * FROM $table_name", ARRAY_A);
    $insert_sql = '';

    foreach ($table_data as $row) {
        $values = array_map(function($value) use ($wpdb) {
            return "'" . $wpdb->_real_escape($value) . "'";
        }, array_values($row));

        $insert_sql .= "INSERT INTO $table_name VALUES (" . implode(', ', $values) . ");\n";
    }

    // Combine structure and data SQL
    $backup_sql = "-- Backup of table $table_name\n\n" . $create_table_sql . $insert_sql;

    // Send as a downloadable file
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="' . $table_name . '_backup.sql"');
    header('Content-Length: ' . strlen($backup_sql));
    echo $backup_sql;
    exit;
}

function unused_table_manager_delete_table($table_name) {
    global $wpdb;
    $wpdb->query("DROP TABLE IF EXISTS $table_name");
}

Please note that this function creates a simple backup of the table structure and data. It does not handle advanced database features such as views, triggers, or stored procedures. For a more comprehensive backup solution, you may want to consider using a dedicated backup plugin or the built-in export functionality provided by phpMyAdmin or similar tools.

  1. Handle the actions based on the $_POST variables.
function unused_table_manager_page() {
    // Check user capabilities
    if (!current_user_can('manage_options')) {
        return;
    }

    // Perform actions based on $_POST variables
    if (isset($_POST['action'])) {
        if ($_POST['action'] == 'backup' && isset($_POST['table'])) {
            unused_table_manager_backup_table($_POST['table']);
        } elseif ($_POST['action'] == 'delete' && isset($_POST['table'])) {
            unused_table_manager_delete_table($_POST['table']);
        }
    }

    // Display the plugin page
    ?>
    <div class="wrap">
        <h1>Unused Table Manager</h1>
        <?php unused_table_manager_list_tables(); ?>
    </div>
    <?php
}
  1. Adjust the action handling for backup and delete actions based on $_GET variables.
function unused_table_manager_page() {
    // Check user capabilities
    if (!current_user_can('manage_options')) {
        return;
    }

    // Perform actions based on $_GET variables
    if (isset($_GET['action']) && isset($_GET['table'])) {
        if ($_GET['action'] == 'backup') {
            unused_table_manager_backup_table($_GET['table']);
        } elseif ($_GET['action'] == 'delete') {
            unused_table_manager_delete_table($_GET['table']);
        }
    }

    // Display the plugin page
    ?>
    <div class="wrap">
        <h1>Unused Table Manager</h1>
        <?php unused_table_manager_list_tables(); ?>
    </div>
    <?php
}

With this code, you have created a basic WordPress plugin that identifies unused tables in the database, lists them, and allows you to download a backup of each table and delete them individually. Please note that the code provided is a starting point and may require further customization to work seamlessly with your specific WordPress installation and to filter the unused tables effectively. Additionally, it is recommended to improve the security and error handling of the plugin for production use.

Notice: before using this code, make a good complete database backup!!

Did you find this article valuable?

Support Theo van der Sluijs by becoming a sponsor. Any amount is appreciated!