Isaac @ MSDN

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.