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.

Comments

• Anonymous
  April 15, 2008
  I think your query should read: WHERE ty.name = 'geography' OR ty.name = 'geometry' Cheers

• Anonymous
  April 16, 2008
  Indeed---good catch!  I've corrected the post. Thanks, -Isaac

• Anonymous
  April 19, 2008
  I think you can do that more easily and generically with the information_schema.columns (although haven't tried it out on SQL Server 2008 (just in prior SQL Servers)) Something of the form SELECT * FROM information_schema.columns WHERE data_type IN('geography', 'geometry') and the above trick works for PostGIS too since both SQL Server and PostgreSQL support the information_schema standard.