List all posts that doesn’t have featured image in WordPress via MySQL

I wanted to set all my posts to have featured image, at the same time I wanted to find my posts that containing any image so I could use it as featured image.

The following is the simple script that I used to list all posts that doesn’t have featured image in WordPress via MySQL, however containing image(s) in the post content.

SELECT * FROM wp_posts
WHERE ID NOT IN (select post_id as p from wp_postmeta where meta_key LIKE "_thumbnail_id")
AND post_type = 'post'
AND post_status = 'publish'
AND post_content REGEXP 'src=".*"'

Once I found them (luckily not many), I edited the post and set the featured image manually.

Then, I used a very handy plugin called Quick Featured Images by Martin Stehle to bulk update the rest of my posts (without featured image and without any image in content).