Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

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

        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

Your Answer


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.