Spatial metadata table in SQL Server 2008

Question

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.

Answer 1

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

Answer 2

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.

• Jonathan Van Matre: 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')"