Retrieving SQL Server Index Properties with INDEXPROPERTY

By:   |   Updated: 2006-08-11   |   Comments   |   Related: More > Indexing

In a previous tip we talked about the built-in function DATABASEPROPERTYEX. With this function you were able to find out information about the various database properties.  SQL Server has other built-in functions that allow you to retrieve other data such as information about indexes.  In prior tip there was a stored procedure that returns information about all of your indexes, but there is other index data that may be helpful to have when analyzing your indexes.

SQL Server 2000 and 2005 have 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.  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 second table below shows a sample output from this query when issued against the Northwind database.

FROM   sysobjects INNER JOIN 
sysindexes ON
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

Some of the things you can find out about your databases include the following information.  For complete lists look here: SQL 2000, SQL 2005.

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

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

            (Source SQL Server 2000 Books Online)

Next Activity

  • 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

Last Updated: 2006-08-11

get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of

View all my tips
Related Resources

Comments For This Article


Recommended Reading

Building SQL Server Indexes in Ascending vs Descending Order

Retaining historical index usage statistics for SQL Server Part 3 of 3

Script out all SQL Server Indexes in a Database using T-SQL

Difference between SQL Server Unique Indexes and Unique Constraints

Creating Indexes with SQL Server Management Studio

get free sql tips
agree to terms

Learn more about SQL Server tools