I have a script that I use to populate the table, which works, kinda, mostly... but I'm not allowed to post it...
But, it uses the Top 1 from each table, and uses this: SP_GEOMETRY.STGeometryType() to get the geometry type of one entry.
Here's what I use, which works:
CREATE TABLE [dbo].[geometry_columns](
[f_table_catalog] [varchar](50) NULL,
[f_table_schema] [varchar](50) NULL,
[f_table_name] [varchar](100) NULL,
[f_geometry_column] [varchar](50) NULL,
[coord_dimension] [int] NULL,
[srid] [int] NULL,
[geometry_type] [varchar](50) NULL
)
and I have entries like this:
f_table_catalog f_table_schema f_table_name f_geometry_column coord_dimension srid geometry_type
GISDB dbo Accuracy SP_GEOMETRY 1 28355 LineString
GISDB dbo AssetAreas SP_GEOMETRY 2 28355 Polygon
QGIS can read these and gives me a list of our ~50 layers instantly.
I also added my SRID to another table spatial_ref_sys:
CREATE TABLE [dbo].[spatial_ref_sys](
[srid] [int] NOT NULL,
[auth_name] [varchar](256) NULL,
[auth_srid] [int] NULL,
[srtext] [varchar](2048) NULL,
[proj4text] [varchar](2048) NULL
) ON [PRIMARY]
entry like this:
srid auth_name auth_srid srtext proj4text
28355 epsg 28355 +proj=utm +zone=55 +south +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs PROJCS["GDA94 / MGA zone 55",GEOGCS["GDA94",DATUM["Geocentric_Datum_of_Australia_1994",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6283"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4283"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",147],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",10000000],AUTHORITY["EPSG","28355"],AXIS["Easting",EAST],AXIS["Northing",NORTH]]
Note that you can get these from spatialreference.org, like this: http://spatialreference.org/ref/epsg/28355/postgis/, just modify it for SQL Server syntax.
Best Answer
Your comment adds more insight as to what you are trying to do. You could just store it as WKT (well known text) Use ArcObjects to get the geometry to WKB, then use something like Sharpmap to convert to WKT.