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.