Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Meta Data FunctionsRSS Feeds

INDEXPROPERTY Function

Posted By: Sarita Patel     Category: SQL Server     Views: 3034

This article explains about INDEXPROPERTY function in sql server with examples.

INDEXPROPERTY function returns property value of specified table id, index name and property name.



Syntax of INDEXPROPERTY Function :

INDEXPROPERTY ( table_id, index, property )

table_id is an identification number of table or indexed view. It is of type integer.

index is a name of index. It is of type nvarchar(128).

property is a name of database property. It is of type varchar(128) and can be of below values.

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

 
Return type of INDEXPROPERTY function is an integer.




Examples of INDEXPROPERTY Function :

Example 1 : Use of INDEXPROPERTY function in select clause

SELECT INDEXPROPERTY( OBJECT_ID('Customers'), 'PK_Customers', 'IndexDepth')

Output
2

Above example returns 2 means index is 2 level depth.
  
Share: 

 
 

Didn't find what you were looking for? Find more on INDEXPROPERTY Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of INDEXPROPERTY Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!