Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a db and want to know if it contains any spatial tables. Is there a table in SQL Server that holds spatial metadata i.e. a list of all the spatial tables in the current db? Oracle has the user_sdo_geom_metadata, Postgis the geometry_columns. What about SQL Server? The only spatial metadata I found was the sys.spatial_references.

share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

You can always resort to querying the INFORMATION_SCHEMA.TABLES and COLUMNS dictionaries to find out if there are tables that are spatially enabled.

share|improve this answer
add comment

Further to unicoletti's answer, here is a SQL query that will list spatial tables and their geometry columns:

select c.TABLE_CATALOG, c.TABLE_NAME as TABLE_NAME, 
        c.TABLE_SCHEMA as TABLE_SCHEMA, c.COLUMN_NAME as COLUMN_NAME 
        from information_schema.columns c join 
        information_schema.tables t ON c.TABLE_NAME = t.TABLE_NAME
        AND t.TABLE_TYPE = 'BASE TABLE' where c.DATA_TYPE = 'geometry'
        order by c.TABLE_SCHEMA, c.TABLE_NAME

If you're looking for geography columns change the geometry to geography in the query.

Also bear in mind a single table can have several spatial columns so it will appear twice in the query.

share|improve this answer
    
The "appear twice in the query" issue can be solved by removing "c.COLUMN_NAME as COLUMN_NAME" from the query, and changing "select" to "select distinct". Also, to get tables that have either spatial type or both, the WHERE clause would be "WHERE c.data_type IN ('geometry','geography')" –  Jonathan Van Matre Dec 16 '11 at 19:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.