Going beyond sp_helpindex

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:

• All information is concatenated in 1 string
• Not much info on partitioned indexes

If I run the following statement:

exec sp_helpindex 'Customer'

The following information will be returned:

index_name	index_description	index_keys
pk_entity	clustered, unique, primary key located on PRIMARY	entity

CREATE FUNCTION dbo.fn_helpindex(@table_name sysname)
RETURNS @result table(table_name            sysname, 
                      index_name            sysname, 
                      is_unique             bit, 
                      ignore_dup_key        bit, 
                      is_primary_key        bit, 
                      is_unique_constraint  bit, 
                      fill_factor           tinyint, 
                      is_padded             bit, 
                      is_disabled           bit, 
                      is_hypothetical       bit, 
                      allow_row_locks       bit, 
                      allow_page_locks      bit, 
                      data_space_type       nvarchar(2),
                      data_space            nvarchar(1024))
AS
  BEGIN
    
    WITH partitionfields ([object_id], [index_id], [name]) AS 
    (SELECT ic.[object_id] ,ic.index_id, c.name 
       FROM sys.index_columns ic
       JOIN sys.columns c 
         ON  ic.[object_id] = c.[object_id] 
        AND ic.column_id = c.column_id 
      WHERE ic.partition_ordinal = 1)
    INSERT INTO @result
    SELECT table_name = object_name(i.[object_id])
         , i.name

         , i.is_unique
         , i.ignore_dup_key
         , i.is_primary_key
         , i.is_unique_constraint
         , i.fill_factor
         , i.is_padded
         , i.is_disabled
         , i.is_hypothetical
         , i.allow_row_locks
         , i.allow_page_locks
         , data_space_type = ds.type
         , data_space = coalesce(ds.name + N'(' + pf.name + N')', ds.name)
    FROM sys.indexes i 
      INNER JOIN sys.data_spaces ds
      ON  i.data_space_id = ds.data_space_id
      LEFT JOIN partitionfields pf
      ON i.[object_id] = pf.[object_id] AND
         i.index_id = pf.index_id
    WHERE i.name IS NOT NULL 
      AND object_name(i.[object_id]) = @table_name

    -- Return the result of the function
    RETURN 
  END

If I run:

select * from dbo.fn_helpindex('Customer')

The following information will be returned:

table_name	index_name	is_clustered	is_unique	ignore_dup_key	is_primary_key	is_unique_constraint	fill_factor	is_padded	is_disabled	is_hypothetical	allow_row_locks	allow_page_locks	data_space_type	data_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.

CREATE PARTITION FUNCTION pfn_maturity_bucket (int)
AS RANGE RIGHT FOR VALUES (3, 6, 12);

CREATE PARTITION SCHEME psc_maturity_bucket
AS PARTITION pfn_maturity_bucket
ALL TO ([PRIMARY])

CREATE TABLE LoanDetails(code nvarchar(50), amount numeric(23,3), maturity_bucket int)
CREATE INDEX ixLoanDetails 
          ON LoanDetails(maturity_bucket) 
          ON psc_maturity_bucket(maturity_bucket)

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

select * from dbo.fn_helpindex('LoanDetails')

The output will be:

table_name	index_name	is_clustered	is_unique	ignore_dup_key	is_primary_key	is_unique_constraint	fill_factor	is_padded	is_disabled	is_hypothetical	allow_row_locks	allow_page_locks	data_space_type	data_space
LoanDetails	ixLoanDetails	0	0	0	0	0	0	0	0	0	1	1	PS	psc_maturity_bucket(maturity_bucket)