STGeometryTypes: Extracting all geometry type string values from a complex geometry

Problem

Ever need to extract a textual description of all geometry types that appear in a single geometry/geography in SQL Server 2008 including all sub-elements?

Background

Recently I had cause to do so and created the following function that implements this need.

Function

USE [GISDB]  -- Change this to your database
GO
--
-- Extracts all geometry type keywords from a geometry (and its sub-elements)
CREATE FUNCTION [$(owner)].[STGeometryTypes](
  @p_geometry geometry 
)
 Returns varchar(MAX)
As
Begin
  Declare
    @v_return varchar(MAX);
  Begin
    If ( @p_geometry is NULL ) 
      return NULL;

    SELECT @v_return = SUBSTRING(a.token,2,LEN(a.token))
      FROM (SELECT (STUFF((SELECT ',' + a.token
                             FROM (select t.token
                                     from [$(owner)].[Tokenizer](@p_geometry.AsTextZM(),' ,)(') as t
							       ) as a
                            WHERE a.token is not null
                              AND a.token not like '%[0-9]%'
                           FOR XML PATH(''), TYPE, ROOT
						  ).value('root[1]','nvarchar(max)'),1,1,'''')
                    ) AS token
            ) as a;

    RETURN @v_return;
   End;
End
GO

Testing

Now let’s test it against a selection of all possible geometry types

-- Simple geometry
select dbo.[STGeometryTypes](geometry::STGeomFromText('POINT(0 1 2)',0)) as gtypes;
go
gtypes
POINT
-- Single CurvePolygon with one interior ring
select [$(owner)].[STGeometryTypes](geometry::STGeomFromText('CURVEPOLYGON(CIRCULARSTRING(0 5, 5 0, 0 -5, -5 0, 0 5), (-2 2, 2 2, 2 -2, -2 -2, -2 2))',0)) as gtypes;
GO
gtypes
CURVEPOLYGON,CIRCULARSTRING

-- GeometryCollection
select  [$(owner)].[STGeometryTypes](
geometry::STGeomFromText(
         'GEOMETRYCOLLECTION(
                  LINESTRING(0 0,20 0,20 20,0 20,0 0), 
                  CURVEPOLYGON(
                       COMPOUNDCURVE(
                               (0 -23.43778, 0 23.43778),
                               CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),
                               (-90 23.43778, -90 -23.43778),
                               CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))), 
                  COMPOUNDCURVE(
                          CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778), 
                          (0 -23.43778, 0 23.43778)))',0)) as gTypes;
GO
gTypes
GEOMETRYCOLLECTION,LINESTRING,CURVEPOLYGON,COMPOUNDCURVE,CIRCULARSTRING,CIRCULARSTRING,COMPOUNDCURVE,CIRCULARSTRING

Farewell

I hope this function is useful to someone.