Get ALL information about spatial index using SQL, including CELLS_PER_OBJECT and USING GEOGRAPHY_AUTO_GRID

Question

When I create a spatial index I can specify some extra parameters, e.g.

CREATE TABLE dbo.Test(
    Id bigint not null,
    Polygon geography not null
)
GO


CREATE SPATIAL INDEX IX_Test_Polygon ON dbo.Test
(
    Polygon
)
USING GEOGRAPHY_AUTO_GRID 
WITH (CELLS_PER_OBJECT = 12)
GO

If I then go to SSMS and script this index, then the script will contain these USING GEOGRAPHY_AUTO_GRID and WITH (CELLS_PER_OBJECT = 12). However, the view sys.indexes does not contain this information.

How can I get this information using SQL, not SSMS.

Answer 1

This information can be found in the system tables, sys.spatial_indexes and sys.spatial_index_tessellations. More info for these system tables at https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/spatial-data-catalog-views?view=sql-server-ver15.