Find tables that DON'T have column with a specific name in Oracle database

Ania - Dataedo Team Ania 2018-12-14

Table of Contents:


    Tables in database often have standard columns, such as creation_date, created_by, modification_date, modified_by.

    Query below lists:

    (A) all tables accessible to the current user in Oracle database that don't have column with a specific name

    (B) all tables in Oracle database that don't have column with a specific name

    Query was executed under the Oracle9i Database version.

    Query

    A. Tables accessible to the current user

    select tables.owner as schema_name,
           tables.table_name
    from sys.all_tables tables
    left join (select owner as schema_name,
                      table_name
               from sys.all_tab_columns 
               where column_name = 'CREATION_DATE'
               ) tables_with_col 
                          on tables.owner = tables_with_col.schema_name
                            and tables.table_name = tables_with_col.table_name
    where tables_with_col.table_name is null
    -- excluding some Oracle maintained schemas
    and tables.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'WKSYS')  
    order by tables.owner, 
             tables.table_name;
    

    B. If you have privilege on dba_tab_columns and dba_tables

    select tables.owner as schema_name,
           tables.table_name
    from sys.dba_tables tables
    left join (select owner as schema_name,
                      table_name
               from sys.dba_tab_columns 
               where column_name = 'CREATION_DATE'
               ) tables_with_col 
                          on tables.owner = tables_with_col.schema_name
                            and tables.table_name = tables_with_col.table_name
    where tables_with_col.table_name is null
    -- excluding some Oracle maintained schemas
    and tables.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'WKSYS')  
    order by tables.owner, 
             tables.table_name;
    

    Columns

    • schema_name - table owner, schema name
    • table_name - table name

    Rows

    • One row represents one table that don't have column with a specific name
    • Scope of rows: (A) all tables accessible to the current user in Oracle database that don't have column with a specific name, (B) all tables in Oracle database that don't have column with a specific name
    • Ordered by schema name, table name

    Sample results

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept