STGeometryTypes: Extracting all geometry type string values from complex geometry

 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

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