, 2018-10-29 (first published: )

create as a system procedure, or in specific database

use: same as sp_helpindex

modify as desired

-- www.qdpma.com/SQL/SqlScripts.html
-- updates 2018-03-06 
-- 2018-04-08 sys.stats is_incremental 
USE master -- skip this for Azure 
 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() 
 raiserror(15250, -1,-1) 
 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, c.name, 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(i.name, '')[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 
--, j.name 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 , t.is_incremental incr 
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' -- skip this for Azure 
--DROP PROCEDURE dbo.sp_helpindex2


1 (1)




1 (1)

Related content

SQL Server Indexes: The Basics

Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.


5,686 reads

Indexed Views With Outer Joins

SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.

3.5 (6)


14,136 reads

Clustering for Indexes

There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.


3,412 reads