Go to content Go to navigation and search

Current SQL Server Blogs

Search

RSS / Atom

Email me

textpattern

Tip #5: Where or where has my little column gone · Nov 24, 05:15 pm by Simon Greener

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.

  Textile Help

<<Tip #4: What Coordinate System is that? >>Tip #6: Correcting invalid geometries