Where are my Spatial Columns?
Hi Folks,
I've been asked a few times how to find out what spatial columns are defined in a database. We don't have any special table for this, but you can easily find out by looking at the usual system views:
SELECT ta.name as table_name, co.name as column_name
FROM sys.tables ta JOIN sys.columns co
ON ta.object_id = co.object_id
JOIN sys.types ty
ON co.user_type_id = ty.user_type_id
WHERE ty.name = 'geography' OR ty.name = 'geometry'
There's nothing special about spatial here: you can replace the type names in the WHERE clause of the query with any other type you'd like to find as well. For example, a simple change finds all integer columns:
SELECT ta.name as table_name, co.name as column_name
FROM sys.tables ta JOIN sys.columns co
ON ta.object_id = co.object_id
JOIN sys.types ty
ON co.user_type_id = ty.user_type_id
WHERE ty.name = 'int'
Cheers,
-Isaac
[16 April 2008]: Updated to correct a typo in the first query.