Find tables that are not used by any other object

Article for: SQL Server Azure SQL Database

Query below lists:

A. all tables accessible to the current user that are not referenced by any object in Oracle database

B. all tables that are not referenced by any object in Oracle database

Query

A. All tables accessible to the current user that are not referenced by any object

select tab.owner as table_schema,
       tab.table_name
from sys.dba_tables tab
left join sys.all_dependencies dep
          on tab.owner = dep.referenced_owner
          and tab.table_name = dep.referenced_name
where dep.referenced_owner is null
      and tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
          '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',
          'OUTLN', 'WKSYS',  'LBACSYS') 
order by tab.owner,
         tab.table_name;

B. If you have privileges on dba_dependencies and dba_tables

select tab.owner as table_schema,
       tab.table_name
from sys.dba_tables tab
left join sys.all_dependencies dep
          on tab.owner = dep.referenced_owner
          and tab.table_name = dep.referenced_name
where dep.referenced_owner is null
      and tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
          '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',
          'OUTLN', 'WKSYS',  'LBACSYS') 
order by tab.owner,
         tab.table_name;

Columns

  • table_schema - schema name of the table
  • table_name - table name

Rows

  • One row represents one table that is not used by any other object
  • Scope of rows: all objects that are not used by any other object
  • Ordered by schema name and table name

Sample results

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