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.