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.

Query

select tab.table_schema as database_name,
       tab.table_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,
         tab.table_name;

Columns

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

Rows

• 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.