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