Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


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.

SELECT sysobjects.name
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

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 MSSQLTips.com.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools