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.
2 Answers
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.
-
1The "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– Jonathan Van Matre2011-12-16 19:50:42 +00:00Commented Dec 16, 2011 at 19:50