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.
SELECT owner, table_name, column_name
WHERE data_type_owner = 'SDE'
ORDER BY owner, table_name;