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.
You can always resort to querying the INFORMATION_SCHEMA.TABLES and COLUMNS dictionaries to find out if there are tables that are spatially enabled.
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.