|
Tip #5: Where or where has my little column gone
Saturday November 24 2007 at 17:15
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.
        
|
SpatialDB Advisor
Home
Comment