Databases often have standard columns. Examples of such standard columns can be id, modified_date, created_by or row_version.
Query below finds all tables that do not have a 'id' column.
select t.table_schema, t.table_name from information_schema.tables t left join (select table_schema, table_name from information_schema.columns where column_name = 'id') c on c.table_name = t.table_name and c.table_schema = t.table_schema where c.table_name is null and t.table_schema not in ('information_schema', 'pg_catalog') and t.table_type = 'BASE TABLE' order by t.table_schema, t.table_name;
- table_schema - name of schema of found table
- table_name - name of found table
- One row represents a table
- Scope of rows: all found tables
- Ordered by schema name, table name
These tables do not have a 'id' column.