The system stored procedures in SQL Server from the very beginning(?) provide useful information. However they have not been updated in a substantial manner for the new features in later versions, nor have they been extended to reflect the need for additional details that are now available for more sophisticated management. Presumably, this is for backward compatibility. As an alternative, there is a provision for creating custom procedures that behave as system procedure with sp_ms_marksystemobject.

In the case of sp_helpindex new features of are included columns, filtered indexes, compression and partitioning. Other information of interest might be size, index usage and statistics. Part of the reason for not changing could be for backward compatibility, which is fine, but let's then make new system procedures with extended information.

In the text of sp_helpindex for SQL Server 2012, the only difference from earlier versions is that the description field has a provision for columnstore. The SQL Server 2014 version adds hash index and memory optimized in the description field.

 

Below is my new procedure for extended index information. I have retained the same error checking code from the original. The cursor loop to assemble the index key columns has been replaced with a code sequence using the STUFF function and FOR XML PATH. A similar structure reports on the included columns. This procedure does not replicate the index description field of the original, but rather has a limited description and a separate field for the type code.

 

USE master

GO

CREATE procedure [dbo].[sp_helpindex3]

 @objname nvarchar(776)

AS

DECLARE @objid int

 , @dbname sysname

 -- Check to see that the object names are local to the current database.

select @dbname = parsename(@objname,3)

if @dbname is null

  select @dbname = db_name()

else if @dbname <> db_name()

begin

  raiserror(15250,-1,-1) 

  return (1)

end

-- Check to see the the table exists and initialize @objid.

 select @objid = object_id(@objname) 

 if @objid is NULL 

 begin 

  raiserror(15009,-1,-1,@objname,@dbname) 

  return (1) 

 end 

 

;WITH b AS (

  SELECT d.object_id, d.index_id, part = COUNT(*)

  , reserved = 8*SUM(d.reserved_page_count)

  , used = 8*SUM(d.used_page_count )

  , in_row_data = 8*SUM(d.in_row_data_page_count)

  , lob_used = 8*SUM(d.lob_used_page_count)

  , overflow = 8*SUM( d.row_overflow_used_page_count)

  , row_count = SUM(row_count)

  , notcompressed = SUM(CASE data_compression WHEN 0 THEN 1 ELSE 0 END)

  , compressed = SUM(CASE data_compression WHEN 0 THEN 0 ELSE 1 END) -- change to 0 for SQL Server 2005

  FROM sys.dm_db_partition_stats d WITH(NOLOCK)

  INNER JOIN sys.partitions r WITH(NOLOCK) ON r.partition_id = d.partition_id

  GROUP BY d.object_id, d.index_id

), j AS (

  SELECT j.object_id, j.index_id, j.key_ordinal, c.column_id, c.name,is_descending_key

  FROM sys.index_columns j

  INNER JOIN sys.columns c ON c.object_id = j.object_id AND c.column_id = j.column_id

)

SELECT ISNULL(i.name, '') [index]

, ISNULL(STUFF(( SELECT ', ' + name + CASE is_descending_key WHEN 1 THEN '-' ELSE '' END

   FROM j WHERE j.object_id = i.object_id AND j.index_id = i.index_id AND j.key_ordinal >0

   ORDER BY j.key_ordinal FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'') ,'') as Keys

, ISNULL(STUFF(( SELECT ', ' + name

   FROM j WHERE j.object_id = i.object_id AND j.index_id = i.index_id AND j.key_ordinal = 0 

   ORDER BY j.column_id FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'') ,'') as Incl

, i.index_id

, CASE WHEN i.is_primary_key = 1 THEN 'PK'

   WHEN i.is_unique_constraint = 1 THEN 'UC'

   WHEN i.is_unique = 1 THEN 'U'

   WHEN i.type = 0 THEN 'heap'

   WHEN i.type = 3 THEN 'X'

   WHEN i.type = 4 THEN 'S'

   ELSE CONVERT(char, i.type) END typ

, i.data_space_id dsi

, b.in_row_data in_row , b.overflow ovf , b.lob_used lob

, b.reserved - b.in_row_data - b.overflow -b.lob_used unu

, 'ABR' = CASE row_count WHEN 0 THEN 0 ELSE 1024*used/row_count END

, y.user_seeks, y.user_scans u_scan, y.user_lookups u_look, y.user_updates u_upd

, b.notcompressed ncm , b.compressed cmp , b.row_count

, s.rows, s.rows_sampled, s.unfiltered_rows, s.modification_counter mod_ctr, s.steps

, CONVERT(varchar, s.last_updated,120) updated

, i.is_disabled dis, i.is_hypothetical hyp, ISNULL(i.filter_definition, '') filt

FROM sys.objects o

JOIN sys.indexes i ON i.object_id = o.object_id

LEFT JOIN b ON b.object_id = i.object_id AND b.index_id = i.index_id

LEFT JOIN sys.dm_db_index_usage_stats y ON y.object_id = i.object_id AND y.index_id = i.index_id

AND y.database_id = DB_ID()

OUTER APPLY sys.dm_db_stats_properties(i.object_id , i.index_id) s

WHERE i.object_id = @objid

GO

 

-- Then mark the procedure as a system procedure.

EXEC sp_ms_marksystemobject 'sp_helpindex3'

GO

SELECT NAME,IS_MS_SHIPPED FROM SYS.OBJECTS WHERE NAME = 'sp_helpindex3'

GO

 

Information from my extended version of index help are space, index usage, and statistics. The DMV/F function dm_db_stats_properties was added in SQL Server (SQL Server 2008 R2 Service Pack 2, SQL Server 2012 Service Pack 1. The function STATS_DATE was added in SQL Server 2008 can be used if dm_db_stats_properties is not supported.

We could also join to dm_db_index_operational_stats, dm_db_index_physical_stats or dm_db_xtp_index_stats for additional information.

It is too bad more statistics information in DBCC SHOW_STATISTICS is not available in query form.