THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang


this is my extended version of sp_helpindex

lets face it, sp_helpindex is old and has not been updated for all the new features of SQL Server introduced since the beginning.

sp_helpindex provides a list of indexes, information on: the type (clustered, unique, primary key, statistics no recompute), and file group, and the key columns

What additional information would we like?

the included column list is one, and also if it is a filtered index, and the filter definition.

Size information is also good, rows, size: in row pages, overflow, lob and unused. Also average bytes per row is useful. If an index is compressed or partitioned, if so, how many partitions and how many partitions are populated.

new sources are: sys.dm_db_index_usage_stats and sys.dm_db_stats_properties
index usage - seeks, scans, lookups, updates
compressed? # of partitions, populated partitions,
difference between current rows and rows when statistics were sampled
rows sampled, modification counter, when statistics were updated

We might also like to know so information from DBCC SHOW_STATISTICS, but this is not available from a query.

USE master



 SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('sp_helpindex2')

) DROP procedure [dbo].sp_helpindex2


CREATE PROCEDURE [dbo].[sp_helpindex2]

 @objname nvarchar(776)


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



return (1)


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

 select @objid = object_id(@objname)

 if @objid is NULL


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

return (1)



;WITH b AS (

SELECT d.object_id, d.index_id, part = COUNT(*) , pop = SUM(CASE row_count WHEN 0 THEN 0 ELSE 1 END)

, 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,, j.is_descending_key, j.is_included_column, j.partition_ordinal

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(, '') [index]

, ISNULL(STUFF(( SELECT ', ' + name + CASE is_descending_key WHEN 1 THEN '-' ELSE '' END + CASE partition_ordinal 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 + CASE partition_ordinal WHEN 1 THEN '*' ELSE '' END

   FROM j WHERE j.object_id = i.object_id AND j.index_id = i.index_id AND (j.is_included_column = 1 OR (j.key_ordinal = 0 AND partition_ordinal = 1) )

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

--, AS ptky

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

, 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.pop, b.part

, rw_delta = 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

, t.no_recompute no_rcp

FROM sys.objects o

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

LEFT JOIN sys.stats t ON t.object_id = o.object_id AND t.stats_id = i.index_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

--LEFT JOIN j ON j.object_id = i.object_id AND j.index_id = i.index_id AND j.partition_ordinal = 1

WHERE i.object_id = @objid


-- Then mark the procedure as a system procedure.

EXEC sys.sp_MS_marksystemobject 'sp_helpindex2'




Published Friday, February 23, 2018 4:20 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Ranga said:

Thanks for sharing. Very handy to know row counts etc. Very informative.

It would be great if the column names are bit descriptive.

March 12, 2018 8:25 AM

jchang said:

feel free to modify the column names as you desire. I am a big proponent of its important to be able to see all important data together, hence the compact column names.

To this end, I recommend 4K, 28-32in monitors. 28in 4K has been low priced for a while. Now even 4K 32in is not too expensive.

March 12, 2018 11:39 AM

Leave a Comment


About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog


Privacy Statement