Find table that DON'T have a column with specific name in MariaDB 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 'version' 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 one table found
  • Scope of rows: all found tables
  • Ordered by: database (schema) name, table name

Sample results

Below is sample with a list of tables that does not have the 'version' column.

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