Derp Derp - 5 months ago 7
SQL Question

Get names of tables where column doesn't exist

In MySQL, what query do I use to get the names of tables where a given column name doesn't exist yet? I have a database where some tables have a

deleted_at
column and some don't, and I want to have the names of the tables that don't have the
deleted_at
column yet.

To prevent confusion, I don't want to check from one single table of it exists, I want a list of all tables that don't have the column. Thanks for help!

Answer

information_schema is useful for this kind of stuff.

SELECT t.table_name 
FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS c 
   ON t.table_schema = c.table_schema
   AND t.table_name = c.table_name
   AND c.column_name = 'deleted_at'
WHERE t.table_schema = 'my_schema'
   AND c.table_name IS NULL
ORDER BY t.table_name;