Retrieving SQL Server Index Properties with INDEXPROPERTY


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


Problem

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.

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. 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,  
       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

Some of the things you can find out about your databases include the following information.

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
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





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


Article Last Updated: 2006-08-11

Comments For This Article





download














get free sql tips
agree to terms