STisGeo: Checking if a column in a table or a view is of type geometry or geography

 USE [GISDB]  /* Replace this with the name of your database */
 GO
 CREATE FUNCTION [dbo].[STisGeo] (
   @schema_name nvarchar(128),
   @object_name nvarchar(128),
   @column_name nvarchar(128)
 )
 RETURNS INTEGER
 WITH EXECUTE AS CALLER,
      RETURNS NULL ON NULL INPUT
 AS
 BEGIN
   DECLARE @v_result VARCHAR(15);
     SELECT @v_result=a.column_type
       FROM (SELECT sc.name AS schema_name,
                     ta.name AS object_name,
                     co.name AS column_name,
                     ty.name AS column_type,
                     'TABLE' AS object_type
               FROM sys.TABLES ta
                    JOIN sys.schemas sc ON sc.schema_id = ta.schema_id
                    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 IN ('geography','geometry')
              UNION ALL
             SELECT sc.name AS schema_name,
                     va.name AS object_name,
                     co.name AS column_name,
                     ty.name AS column_type,
                     'VIEW' AS object_type
               FROM sys.views va
                    JOIN sys.schemas sc ON sc.schema_id = va.schema_id
                    JOIN sys.COLUMNS co ON va.object_id = co.object_id
                    JOIN sys.types ty   ON co.user_type_id = ty.user_type_id
              WHERE ty.name IN ('geography','geometry')
             ) a
         WHERE a.schema_name = @schema_name
           AND a.object_name = @object_name
           AND a.column_name = @column_name ;
     RETURN CASE WHEN @v_result IS NULL       THEN -1
                 WHEN @v_result = 'geography' THEN  1
                 ELSE 0
            END;
 END;

 CREATE TABLE dbo.foo (foo_id INTEGER, geog geography, geom geometry);
 SELECT dbo.STISGEO('dbo',NULL,'geog') AS isGeo;
 isGeo
 NULL
 SELECT dbo.STISGEO('dbo','foo','geog') AS isGeo;
 isGeo
 1
 SELECT dbo.STISGEO('dbo','foo','geom') AS isGeo;
 GO
 isGeo
 0
 CREATE VIEW dbo.vw_foo AS SELECT foo_id, geog FROM dbo.foo;
 SELECT dbo.STISGEO('dbo','foo','geom') AS isGeo;
 isGeo
 0
 SELECT dbo.STISGEO('dbo','foo','geog') AS isGeo;
 isGeo
 1
 DROP VIEW  dbo.vw_foo;
 DROP TABLE dbo.foo;