Problem
SQL Server has several functions to assist with meta data about the server, databases, indexes, and more. In this tip, we will look at how to get index information for indexes in a database using the INDEXPROPERTY function.
Solution
SQL Server has a built-in function called INDEXPROPERTY that allows you to return specific information about an index. This function can be called from a SELECT statement to return the results of one or more indexes.
Example Use of INDEXPROPERTY function
So to find out the Index Fill Factor for all of your indexes in one of your databases you can run the following query. The table below shows sample output from this query when issued against a database.
SELECT sysobjects.name,
sysindexes.name,
INDEXPROPERTY(OBJECT_ID(sysobjects.name),sysindexes.name,'IndexFillFactor')
FROM sysobjects INNER JOIN
sysindexes ON sysobjects.id = sysindexes.id
WHERE xtype = 'U' | Table | Index | IndexFillFactor |
|---|---|---|
| Orders | CustomerID | 80 |
| Orders | CustomersOrders | 80 |
| Orders | EmployeeID | 80 |
| Orders | EmployeesOrders | 80 |
| Orders | OrderDate | 80 |
| Orders | ShippedDate | 80 |
| Orders | ShippersOrders | 80 |
| Orders | ShipPostalCode | 80 |
| Products | PK_Products | 80 |
| Products | CategoriesProducts | 80 |
| Products | CategoryID | 80 |
| Products | ProductName | 80 |
| Products | SupplierID | 80 |
| Products | SuppliersProducts | 80 |
INDEXPROPERTY Function Options
Above we used IndexFillFactor, below you can see all of the other properties you can return from this function.
| Property | Description |
|---|---|
| IndexDepth | Depth of the index. Returns the number of levels the index has. |
| IndexFillFactor | Index specifies its own fill factor. Returns the fill factor used when the index was created or last rebuilt. |
| IndexID | Index ID of the index on a specified table or indexed view. |
| IsAutoStatistics | Index was generated by the auto create statistics option of sp_dboption. 1 = True 0 = False NULL = Invalid input |
| IsClustered | Index is clustered. 1 = True 0 = False NULL = Invalid input |
| IsDisabled | Index is disabled. |
| IsFulltextKey | Index is the full-text key for a table. 1 = True 0 = False NULL = Invalid input |
| IsHypothetical | Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column level statistics. 1 = True 0 = False NULL = Invalid input |
| IsPadIndex | Index specifies space to leave open on each interior node. 1 = True 0 = False NULL = Invalid input |
| IsPageLockDisallowed | 1 = Page locking is disallowed through sp_indexoption. 0 = Page locking is allowed. NULL = Invalid input |
| IsRowLockDisallowed | 1 = Row locking is disallowed through sp_indexoption. 0 = Row locking is allowed. NULL = Invalid input. |
| IsStatistics | Index was created by the CREATE STATISTICS statement or by the auto create statistics option of sp_dboption. Statistics indexes are used as a placeholder for column-level statistics. 1 = True 0 = False NULL = Invalid input |
| IsUnique | Index is unique. 1 = True 0 = False NULL = Invalid input |
| IsColumnstore | Index is a columnstore index |
| IsOptimizedForSequentialKey | Index has optimization for last-page inserts enabled. |
Next Steps
- Take a look at this built-in index property function and how you can use it to document your databases or easily find out the index settings in your databases
- Use this function to audit your databases to see what the index settings are
- Use this along with this stored procedure to fully document your database indexes
- Read this tip about the built-in function DATABASEPROPERTYEX to get database properties.
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
