FoxTricks

Going beyond sp_help_index

Every SQL Server developer and DBA has to use the sp_helpindex from time to time to get some information on the indexes on a table.

I have 2 major problems with this stored procedure:

If I run the following statement:

The following information will be returned:

 

index_nameindex_descriptionindex_keys
pk_entity clustered, unique, primary key located on PRIMARY entity

 

To improve the way to work with this information I built the following function

If I run:

The following information will be returned:

table_nameindex_nameis_clusteredis_uniqueignore_dup_keyis_primary_keyis_unique_constraintfill_factoris_paddedis_disabledis_hypotheticalallow_row_locksallow_page_locksdata_space_typedata_space
Customer pk_entity 1 1 0 1 0 0 0 0 0 1 1 FG PRIMARY

If we use the function on a table with a partitioned index (like the script below) the function will return more information about the data space as well.

So, let's run the following statement on the partitioned table:

The output will be:

table_nameindex_nameis_clusteredis_uniqueignore_dup_keyis_primary_keyis_unique_constraintfill_factoris_paddedis_disabledis_hypotheticalallow_row_locksallow_page_locksdata_space_typedata_space
LoanDetails ixLoanDetails 0 0 0 0 0 0 0 0 0 1 1 PS psc_maturity_bucket(maturity_bucket)