Where are my Spatial Columns?

Where are my Spatial Columns?

  • Comments 3

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.

  • I think your query should read:

    WHERE ty.name = 'geography' OR ty.name = 'geometry'

    Cheers

  • Indeed---good catch!  I've corrected the post.

    Thanks,

    -Isaac

  • 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.

Page 1 of 1 (3 items)