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.

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.