Find table that DON'T have a column with specific name in MySQL database

Databases often have standard columns. Examples of such standard columns can be id, modified_date, created_by or row_version.

The query below finds all tables that do not have the specified column.


select tab.table_schema as database_name,
from information_schema.tables tab
left join information_schema.columns col
          on tab.table_schema = col.table_schema
          and tab.table_name = col.table_name
          and col.column_name = 'id'    -- put column name here
where tab.table_schema not in ('information_schema', 'mysql',
                           'performance_schema', 'sys')
      and tab.table_type = 'BASE TABLE'
      and col.column_name is null
order by tab.table_schema,


  • database_name - name of the database (schema) of the table found
  • table_name - name of the table found


  • One row: represents a table
  • Scope of rows: all found tables
  • Ordered by: database (schema) name

Sample results

List of tables that don't have the last_update column.

There are no comments. Click here to write the first comment.