Andy Brewer passed me this, adapted from http://barendgehrels.blogspot.co.uk/2011/04/extent-of-sql-server-spatial-table.html
This is a useful script, using CTEs (common table expressions) to calculate the extent of the geometry in a SQL Server table. Replace “geom” with the name of your geometry column and “MyTable” with the name of your table.
Note that this script depends on STEnvelope returning a rectangular extent with its first point at the SW corner and its third in the NE corner. Tests from the author of the blog above confirm the SQL Server currently does this.
select geom.STEnvelope() as envelope from MyTable
select envelope.STPointN(1) as point from cte_envelope
select envelope.STPointN(3) from cte_envelope
select min(point.STX) as min_x, min(point.STY) as min_y, max(point.STX) as max_x, max(point.STY) as max_y