7

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.

Improve this question

2 Answers 2

Reset to default
7

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

Improve this answer
6

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.

Improve this answer
1
  • 1
    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
    Commented Dec 16, 2011 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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