Discover all tables and views in an Oracle instance which have a st_geometry attribute

Summary

Instructions provided describe how to discover all tables or views in an Oracle instance which have a st_geometry attribute with the given SQL syntax.

It may become necessary to know which schemas have tables or views containing a st_geometry attribute in the case where the SDE schema must be dropped. If the SDE schema is removed prior to removing the st_geometry attribute from existing tables, once the SDE schema has been dropped (thus the removal of the st_geometry type), the tables may no longer be accessible.

Procedure

Code:
SELECT owner, table_name, column_name
FROM dba_tab_columns
WHERE data_type_owner = 'SDE'
ORDER BY owner, table_name;