I need to get the name of the coordinate system of a geometry type field in SQL Server 2008 R2 (for example NAD_1983_UTM_Zone_14N or GCS_WGS_1984 or other). Is this possible?
In 2008, I usually do
select SHAPE.STSrid from dbo.STATES
(where SHAPE is the geometry column and dbo.states is the table name). It will give you SRID.
Now you can run
select * from sys.spatial_reference_systems where spatial_reference_id = 4326
However, as @Jay Cummins mentioned, it seems to have only geographic coordinate systems. Link to the Microsoft forum: Projected Coordinate System Support
If you would have an Esri geodatabase, you could see the projected coordinate system SRID and the name with definition from ST_SPATIAL_REFERENCE_SYSTEMS which is created when creating a geodatabase repository.