With most of my projects I try not to delete data from my database. As part of this we have a “deleted” column in every table that keeps track of deleted data so it’s easy to restore and keeps valid FK relationships. Some tables (status tables, tables that form the basis for select elements) never have elements deleted so we don’t worry about this. The other day we needed to make sure every table had a deleted column.

In order to fix this we looked at the information_schema database that MySQL creates. It’s a set of dynamically generated tables that allow you to SQL your way through your database’s layout.

Below is the select we created to find these missing columns:

select TABLES.TABLE_NAME from information_schema.TABLES
left join information_schema.COLUMNS on TABLES.TABLE_NAME = COLUMNS.TABLE_NAME and TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA and COLUMNS.COLUMN_NAME = 'deleted'
where TABLES.TABLE_SCHEMA = '{yourschemaname}' and COLUMNS.TABLE_NAME is null