Find locations of all featured images of draft posts via SQL

Alright this worked for me:

First run the bellow script to generate the file locations of all files used for draft posts.

SELECT voybp_posts.guid
FROM voybp_posts
WHERE voybp_posts.ID IN (SELECT voybp_postmeta.meta_value
                         FROM voybp_postmeta
                         WHERE voybp_postmeta.post_id IN (
                             SELECT voybp_posts.ID
                             FROM voybp_posts
                             WHERE voybp_posts.post_status="draft")
                         AND voybp_postmeta.meta_key="_thumbnail_id")

Export this as a CSV file and save it to desktop, then using notepad++ or a similar program use find/replace to add rm to the beginning of every line.

Next SSH into your server and execute the rm commands.

Next you modify the above script and run it to delete the attachment files in the database that lead to nothing.

DELETE
FROM voybp_posts
WHERE voybp_posts.ID IN (SELECT voybp_postmeta.meta_value
                         FROM voybp_postmeta
                         WHERE voybp_postmeta.post_id IN (
                             SELECT voybp_posts.ID
                             FROM voybp_posts
                             WHERE voybp_posts.post_status="draft")
                         AND voybp_postmeta.meta_key="_thumbnail_id")

Finally we find and delete the draft posts.

DELETE
FROM voybp_posts
WHERE voybp_posts.post_status="draft"
AND voybp_posts.post_type="post"

Hope this helps