Tip #5: Where or where has my little column gone

In Oracle I often want to know the name of the columns that are of type SDO_GEOMETRY. Why? Because the Oracle metadata tables exposed to each user via USER_SDO_GEOM_METADATA may not have an entry for a particular table/column pair.

The query is relatively easy:

gis@XE> select table_name, column_name, data_type
  2 from user_tab_columns
  3 where data_type = 'SDO_GEOMETRY'
  4 order by 1;
TABLE_NAME                     COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------ -------------
BASS_GRID                      GEOMETRY                       SDO_GEOMETRY
CONVEX                         GEOM                           SDO_GEOMETRY
DELAUNAY                       GEOM                           SDO_GEOMETRY
FAUNA                          GEOM                           SDO_GEOMETRY
FAUNA                          MAPSHEET                       SDO_GEOMETRY
LINE_REL                       GEOM                           SDO_GEOMETRY
PILOT_EDGE                     GEOMETRY                       SDO_GEOMETRY
7 rows selected.

In SQL Server one can get the same information via the following query:

select a.name as table_name,
       b.name as column_name,
       c.name as column_type
  from sysobjects a,
       syscolumns b,
       systypes C
  where a.type = 'U'
    and a.id = b.id
    and b.usertype = c.usertype
    and c.name in ('geometry','geography')
  order by 1,2;

table_name            column_name      column_type
ADMIN                 Geom             geography
ADMIN                 Geom             geometry
Convex                Geom             geography
Convex                Geom             geometry
COUPE                 Geom             geography
COUPE                 Geom             geometry
Delaunay              Geom             geography
Delaunay              Geom             geometry
my_multi_points       geom             geography
my_multi_points       geom             geometry
my_points             geom             geography
my_points             geom             geometry
my_polygons           geom             geography
my_polygons           geom             geometry
TRANSPORT             Geom             geography
TRANSPORT             Geom             geometry

Hope this is of use.