Query to delete all featured images and remove post meta?

Question

I'd like to run an SQL query to find all featured images from posts and delete the file itself and any references to it.

Can anyone tell me how to accomplish this? Many thanks!

Answer 1

A MYSQL Query cannot delete the files themselves. You will have to use wp_delete_attachment(). The following is a proof-of-concept for you to alter how you please. The featured image is stored in postmeta as _thumbnail_id. wp_delete_attachment() does the rest for you.

<?php
/*
Plugin Name: Delete All Featured Images
Description: Delete all featured images by visiting /?delete-featured-images=1
Version: 0.1
Author: Brian Fegter
Author URI: http://coderrr.com
License: GPL3v2
*/

# USAGE: visit http://yourdomain.com/?delete-featured-images=1

add_action('init', 'foo_bar_delete_featured', 0);
function foo_bar_delete_featured(){

    # Check for logged in state
    if(!is_user_logged_in())
        return;

    # Check for admin role
    if(!current_user_can('manage_options'))
        return;

    # Check for query string
    if(isset($_GET['delete-featured-images']) && $_GET['delete-featured-images'] == 1){
        global $wpdb;

        # Run a DQL to get all featured image rows
        $attachments = $wpdb->get_results("SELECT * FROM $wpdb->postmeta WHERE meta_key = '_thumbnail_id'");

        foreach($attachments as $attachment){

            # Run a DML to remove this featured image row
            $wpdb->query("DELETE FROM $wpdb->postmeta WHERE meta_id = '$attachment->meta_id' LIMIT 1");

            # Delete attachment DB rows and files
            wp_delete_attachment($attachment->meta_value, true);

            # Print to screen
            show_message('Attachment #$attachment->meta_value deleted.');
        }
        exit;
    }
}

Answer 2

Use this query to remove thumbnails images and associated metadata from database

global $wpdb;

$attachments = $wpdb->get_results( "
     SELECT * 
     FROM $wpdb->postmeta 
     WHERE meta_key = '_thumbnail_id'
" );

foreach ( $attachments as $attachment ) {
    wp_delete_attachment( $attachment->meta_value, true );
}

$wpdb->query( "
    DELETE FROM $wpdb->postmeta 
    WHERE meta_key = '_thumbnail_id'
" );