How can I find the trashed or deleted posts in a WordPress database?

I’m working on a migration, been given a wordpress site, and I’m intended to migrate it to a new one, maded by me. I need to do a script to get all the useful posts (whitout the draft, the trashed or deleted ones and so on). But I don’t know the data base logic to tag the diferents type of posts.

Answer

Well on the old installation, you still can clean your database and keep only published post using a Plugin like Trash emptier

Otherwise Warning !!! This must be done After a DB Backup !!! On MySql you can execute these queries:

STEP 1: Check query results using SELECT:

SELECT *
FROM wp_posts a
LEFT JOIN wp_postmeta b ON ( a.ID = b.post_id )
WHERE post_status = 'trash';

STEP 2 : Delete when sure about the targeted posts :

delete a,b
FROM wp_posts a
LEFT JOIN wp_postmeta b ON ( a.ID = b.post_id )
WHERE post_status = 'trash';

There are other post_status you can check on your database and that you would like to purge too. like auto-draft or draft …