SQL Server – Transact SQL – List Index Info

Introduction

Percy Reyes, Microsoft SQL Server MVP, has a very nice utility that gives detailed insight into Indexes.

I have used it for a few months now.

And, it has available @

SQL Server Index Report with Included Columns, Storage and more for all Tables in a Database
https://www.mssqltips.com/sqlservertip/3450/sql-server-index-report-with-included-columns-storage-and-more-for-all-tables-in-a-database/

 

Customized

I customized it a little bit in the following areas:

  1. Replaced the cursors used for gathering key and included columns with set based Common table Expression (cte)

 

 

Code

Stored Procedure

use master
go

if object_id('[dbo].[sp_helpindexInfo]') is null
begin

	exec('create procedure  [dbo].[sp_helpindexInfo] as select 1/0 as [shell] ')

end
go

ALTER proc  [dbo].[sp_helpindexInfo] 
( 
	  @SchemaName			sysname=NULL
	, @TableName			sysname=NULL
	, @IndexName			sysname=NULL 
	, @dataspace			sysname=NULL
	, @skipDisabled			bit = 1
	, @skipXMLIndexes		bit = 1
	, @debug				bit = 0
	, @allocationUnitType	varchar(600) = 'IN_ROW_DATA'
)
AS

BEGIN

	/*
		Revision History

		dadeniji 2016.05.27
			a) Filtering on @objectID in the CTEs to speed things up


		dadeniji 2016.04.06
			a) Added support for XML indexes. 
			b) Querying sys.dm_db_index_physical_stats

		dadeniji 2016.09.17
			a) Added Index Stats
			a) https://msdn.microsoft.com/en-us/library/ms174384.aspx


	*/
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	set transaction isolation level read uncommitted;

	declare @objectName sysname
	declare @objectID   int
	declare @indexID    int

	declare @mode		sysname

	declare @tblAllocationUnitType TABLE
	(
		  [ItemNumber] int
		, [Item]	   varchar(255)
	)

	declare @tblResult TABLE
	(

		  [id]					    int not null identity(1,1)

		, [object_id]				int not null
		, index_id					int not null

		, [schemaName]				sysname
		, [tableName]				sysname
		, [indexName]				sysname

		, [indexKeyColumns]			varchar(4000)
		, [indexIncludedColumns]	varchar(4000)
		, [NumberofRows]			bigint
		, [Indexsize]				bigint

		, [is_unique]				int
		, [type_desc]				sysname
		, [fill_factor]				float
		, [is_disabled]				bit

		, dataspaceid				int
		, dataspaceName				sysname

		, is_padded					bit
		, [allow_page_locks]		bit
		, [allow_row_locks]			bit
		, IsAutoStatistics			bit
		, [ignore_dup_key]			bit

		, [allocation_unit_id]		bigint
		, [allocationUnitType]		int
		, [allocationUnitTypeDesc]	sysname null
		, [container_id]			bigint  null

		, [bufferNumberofPages]		int	null
		, [bufferMB]				int null

		, [density]					decimal(30,15) null
		, [averageLength]			decimal(30,15) null
		, [densityColumn]					varchar(1000) null	


		, primary key
			(
			     [object_id]
				,[index_id]
			)

	)


	declare @tblIndexPhysicalStatsCandidate TABLE
	(
		  [id]			int 
							identity(1,1)
		, [object_id]	int
		, index_id		int
	)

	declare @tblIndexPhysicalStats TABLE
	(
		  [id]							  int

		, [object_id]					  int

		, index_id						  int

		, [NumberofRows]				  bigint

		, page_count					  int

		--, avg_page_space_used_in_percent  float
		, page_size						  int

		--, page_size_used
		--	as ( page_size_allocated * avg_page_space_used_in_percent)

	)

	/*
		sys.dm_db_index_physical_stats (Transact-SQL)
			https://msdn.microsoft.com/en-us/library/ms188917.aspx

		The DETAILED mode scans all pages and returns all statistics.
		The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. 
		To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. 
		It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA 
		   allocation unit of the index.
	*/

	declare @tblIndexStatsDensityVector TABLE
	(
		  [id]							  int

		, [object_id]					  int

		, index_id						  int

		, [density]						  decimal(30,15)

		, [averageLength]				  decimal(30,15)

		, [columns]						  varchar(1000) null	

	)

	declare @tblIndexStatsDensityVectorStaging TABLE
	(
		  [id]				 int not null identity(1,1)

		, [density]			 decimal(30,15)

		, [averageLength]	 decimal(30,15)

		, [columns]			 varchar(800) null

	)



	declare @INDEX_TYPE_XML		tinyint

	declare @objectIDCand		int
	declare @indexIDCand		int	

	declare @candID				int
	declare @candIDMax			int

	declare @FORMAT_SQL_DBCC_SHOW_STATISTICS varchar(600)
	declare @sql							 varchar(600)
	
	set @FORMAT_SQL_DBCC_SHOW_STATISTICS
				= 'DBCC SHOW_STATISTICS '
					+ ' ( ''%s'', ''%s'' )'
					+ '	WITH DENSITY_VECTOR ';

	set @INDEX_TYPE_XML = 3

	--set @mode = 'detailed'
	--set @mode = 'sampled'
	set @mode = 'limited'

	if (
			    ( @SchemaName is not null )
			and ( @TableName is not null )
		)
	begin

		set @objectName = @SchemaName + '.' + @TableName

		set @objectID = object_id(@objectName)

	end

	else if 
		(
			    ( @SchemaName is null )
			and ( @TableName is not null )
		)
	begin

		set @objectName = @TableName

		set @objectID = object_id(@objectName)

	end

	if (
			    (@objectID is not null )
			and (@IndexName is not null )
	   )	
	begin
	
		select @indexID = index_id
		from   sys.indexes tblSI
		where  tblSI.object_id = @objectID
		and    tblSI.[name] = @IndexName

	end


	/*
			select
					  [object_id] = @objectID --NULL
					, [index_id] = @indexID --NULL
					, [mode] = @mode
					, [@SchemaName] = @SchemaName
					, [@TableName] = @TableName
					, [@indexName] = @indexName
	
		--return

	*/


	insert into @tblAllocationUnitType
	(
		  [ItemNumber]
		, [Item]	  
	)
	select 
			  tblSP.[ItemNumber]
		    , ltrim(rtrim(tblSP.[Item]))
	from   [dbo].[DelimitedSplit8K]
			(
				  @allocationUnitType
				, ','
			) tblSP

	if (@skipXMLIndexes = 0)
	begin

		/*
			Get list of XML Indexes
		*/
		insert into @tblIndexPhysicalStatsCandidate
		(
			  [object_id]
			, [index_id]	
		)

		select
			  tblObject.[object_id]
			, tblIndex.[index_id]

		from    sys.objects tblObject

		inner join sys.schemas tblSchema

				on tblObject.schema_id = tblSchema.schema_id

		inner join sys.indexes tblIndex

				on tblObject.object_id = tblIndex.object_id

		inner join sys.data_spaces da 

				on da.data_space_id= tblIndex.data_space_id

		where  tblIndex.[type] in (@INDEX_TYPE_XML)
			
		and    tblSchema.[name] = 
							case 
								when @SchemaName is null then tblSchema.[name]
								when @SchemaName = '' then tblSchema.[name]
								else @SchemaName
							end

		 and   tblObject.[name] =
						case 
							when @TableName is null then tblObject.[name]
							when @Tablename = ''  then tblObject.[name]
							else @TableName
						 end

		 and   tblIndex.[name] =
						case 
							when @IndexName is null then tblIndex.[name]
							when @Indexname = ''  then tblIndex.[name]
							else @IndexName
						end

		 and   da.name =
						case 
							when @dataspace is null then da.name
							when @dataspace = ''  then da.name
							else @dataspace
						end


		and   [tblObject].[object_id]
					 = isNull(
								  @objectID
								, [tblObject].[object_id]
							 )


		/*
			Msg 2530, Level 16, State 1
			The index "32000" on table "CourseManagerUserLog" is disabled.
		*/
		and tblIndex.[is_disabled] = 0

		set @candID = 1
		set @candIDMax = ( select max([id]) from @tblIndexPhysicalStatsCandidate)

		/*
			Iterate XML Index list
		*/
		while (@candID <= @candIDMax)
		begin

			/*
				Get Candidate data
			*/
			select
					  @objectIDCand = tblIPSC.[object_id]
					, @indexIDCand = tblIPSC.[index_id]	

			from    @tblIndexPhysicalStatsCandidate tblIPSC

			where   tblIPSC.[id] = @candID

			/*
				Get Stats
			*/
			insert into @tblIndexPhysicalStats
			(
				  [id]
				, [object_id]
				, index_id	
				, [NumberofRows]
				, page_count

				--, avg_page_space_used_in_percent
				, page_size	

				--, page_size_used
				--	as ( page_size_allocated * avg_page_space_used_in_percent)

			)
			select 
					  @candID

					, [objectID]
						= @objectIDCand -- tblSI.OBJECT_ID

					, [indexID]
						= @indexIDCand --tblSI.index_id

					, record_count
						= sum(tblIS.[record_count])

					, page_count
						= sum(tblIS.page_count)

					, page_size
						= CONVERT
							(
								DECIMAL(16, 1)
								, CAST( 8 * sum(tblIS.page_count)/1024.0 AS DECIMAL(20,1))

							)

					--, avg_page_space_used_in_percent 
					--	= avg(tblIS.avg_page_space_used_in_percent)

			from sys.dm_db_index_physical_stats
			 --cross apply master.dbo.tvf_dm_db_index_physical_stats
			 (
					db_id()
				
				  , @objectIDCand

				  , @indexIDCand

				  , NULL

				  , @mode

			) tblIS 




			set @candID = @candID + 1


		end

	end -- if (@skipXMLIndexes = 0)


	set @candID = 1
	set @candIDMax = ( select max([id]) from @tblIndexPhysicalStatsCandidate)



	declare @tblOSBufferDescriptors TABLE
	(
		  allocation_unit_id	bigint
		, [NumberofPages]		bigint
		, [bufferMB]			float
	)

	;with cteOSBufferDescriptors
	(
		  allocation_unit_id
		, [NumberofPages]
		, [bufferMB]
	)
	as
	(

		SELECT 
				  tblOSBD.allocation_unit_id
				--, tblOSBD.page_type
				, [NumberofPages]
					= count(*)
				, [bufferMB]
					= CONVERT
						(
							  DECIMAL(12,2)
							, (
								CAST(
										 COUNT(*) 
										  as bigint
									)
								* CAST(8 as float)
							)
							/ 1024
						) 

		FROM sys.dm_os_buffer_descriptors tblOSBD

		group by
				  tblOSBD.allocation_unit_id
				--, tblOSBD.page_type

	)
	insert into @tblOSBufferDescriptors
	(
		  allocation_unit_id
		, [NumberofPages]
		, [bufferMB]
	)
	select 
		  allocation_unit_id
		, [NumberofPages]
		, [bufferMB]

	from cteOSBufferDescriptors


	-- getting the index sizes
	; with cteIndex
	(
		  [object_id]
		, [index_id]
	)
	as
	(

		select
				  tblIndex.object_id
				, tblIndex.index_id

		from    sys.objects tblObject

		inner join sys.schemas tblSchema

				on tblObject.schema_id = tblSchema.schema_id

		inner join sys.indexes tblIndex

				on tblObject.object_id = tblIndex.object_id


		inner join sys.data_spaces da 

				on da.data_space_id= tblIndex.data_space_id


	   where   tblSchema.[name] = 
						case 
							when @SchemaName is null then tblSchema.[name]
							when @SchemaName = '' then tblSchema.[name]
							else @SchemaName
						end

		 and   tblObject.[name] =
						case 
							when @TableName is null then tblObject.[name]
							when @Tablename = ''  then tblObject.[name]
							else @TableName
						 end

		 and   tblIndex.[name] =
						case 
							when @IndexName is null then tblIndex.[name]
							when @Indexname = ''  then tblIndex.[name]
							else @IndexName
						end

		 and   da.name =
						case 
							when @dataspace is null then da.name
							when @dataspace = ''  then da.name
							else @dataspace
						end


	)
	
	, ctePartition
	(
		  [OBJECT_ID]
		, index_id
		, data_space_id
		, [type]
		, [container_id]
		, record_count
		, total_pages
		, used_pages

	)
	as
	(

		select 
				  p.OBJECT_ID
				, p.index_id
				, a.data_space_id
				, a.[type]
				, a.container_id
				, record_count
					= sum(p.rows)
				, page_count
					= sum(a.total_pages)
				, used_pages
					= sum(a.used_pages)

		from  sys.partitions AS p 

		inner join sys.allocation_units AS a 

			ON (
					(
						    ( a.[type] in ( 1, 3) )						
						AND ( a.container_id = p.[hobt_id] )
					)

					OR
					(
						    ( a.[type] = 2 )						
						AND ( a.container_id = p.[partition_id] )
					)

				)

		where  [p].[object_id]
					 = isNull(
								  @objectID
								, [p].[object_id]
							 )

		group by
				  p.OBJECT_ID
				, p.index_id
				, a.data_space_id
				, a.[type]
				, a.container_id

	)

	, cteAllocationUnit
	(
		  [object_id]
		, index_id 
		, allocation_unit_id
		, [type]
		, [type_desc]
		, container_id
		, data_space_id
	)
	as
	(

		SELECT 
				  [object_id]
				, index_id 
				, allocation_unit_id
				, tblAU.[type]
				, tblAU.[type_desc]
				, tblAU.container_id
				, tblAU.data_space_id

		FROM sys.allocation_units AS tblAU

		INNER JOIN sys.partitions AS tblP

				ON tblAU.container_id = tblP.hobt_id 
				AND 
				(
					   tblAU.[type] = 1 
					OR tblAU.[type] = 3
				)

		where  tblP.[object_id]
					 = isNull(
								  @objectID
								, tblP.[object_id]
							 )



		UNION ALL


		SELECT 
				  object_id
				, index_id
				, allocation_unit_id
				, tblAU.[type]
				, tblAU.[type_desc]
				, tblAU.container_id
				, tblAU.data_space_id

		FROM sys.allocation_units AS tblAU

		INNER JOIN sys.partitions AS tblP 

			ON tblAU.container_id = tblP.[partition_id]
			AND tblAU.[type] = 2

		where  tblP.[object_id]

					 = isNull(
								@objectID
								, tblP.[object_id]
							 )



	)

	, cteOSBufferDescriptors
	as
	(

		SELECT 
				  tblOSBD.allocation_unit_id
				--, tblOSBD.page_type
				, [NumberofPages]
					= count(*)
				, [bufferMB]
					= CONVERT
						(
							  DECIMAL(12,2)
							, (
								CAST(
										 COUNT(*) 
										  as bigint
									)
								* CAST(8 as float)
							)
							/ 1024
						) 

		FROM sys.dm_os_buffer_descriptors tblOSBD

		group by
				  tblOSBD.allocation_unit_id
				--, tblOSBD.page_type

	)

	, cteIndexColumnsKey
	(
		  object_id
		, index_id
		, [columnNames]
	)
	as
	(

		select
				  tblSI.object_id
				, tblSI.index_id
				, Stuff
					(
						(
							Select 
									', ' 
									+ tblSC.[name]
									+ case tblSIC.is_descending_key
											when 1 then '-'
											else ''
									  end

							from  sys.index_columns  tblSIC

							inner join sys.columns  tblSC

							on   tblSIC.object_id = tblSC.object_id
							and  tblSIC.column_id = tblSC.column_id

							where tblSI.object_id = tblSIC.object_id
							and   tblSI.index_id = tblSIC.index_id

							and   tblSIC.is_included_column = 0

							order by 
									tblSIC.key_ordinal

					        For Xml Path('')
						)
						, 1
						, 2
						, ''
					) As Columns

		from   sys.indexes tblSI

		where  [tblSI].[object_id]
					 = isNull(
								  @objectID
								, [tblSI].[object_id]
							 )



	)


	, cteIndexColumnsIncluded
	(
		  object_id
		, index_id
		, [columnNames]
	)
	as
	(

		select 
				  tblSI.object_id
				, tblSI.index_id
				, Stuff
					(
						(
							Select 
									', ' 
									+ tblSC.[name]
									+ case tblSIC.is_descending_key
											when 1 then '-'
											else ''
									  end

							from  sys.index_columns  tblSIC

							inner join sys.columns  tblSC

							on   tblSIC.object_id = tblSC.object_id
							and  tblSIC.column_id = tblSC.column_id

							where tblSI.object_id = tblSIC.object_id
							and   tblSI.index_id = tblSIC.index_id
							and   tblSIC.key_ordinal = 0

							and   tblSIC.is_included_column = 1

							order by 
									tblSIC.index_column_id

					        For Xml Path('')
						)
						, 1
						, 2
						, ''
					) As Columns

		from   sys.indexes tblSI

		where  [tblSI].[object_id]
					 = isNull(
								  @objectID
								, [tblSI].[object_id]
							 )


	)

	insert into @tblResult
	(

		  [object_id]
		, [index_id]

		, [schemaName]
		, [TableName]
		, [indexName]

		, [indexKeyColumns]
		, [indexIncludedColumns]
		, [numberofRows]
		, [Indexsize]			

		, is_unique				
		, [type_desc]				
		, fill_factor			
		, is_disabled
					
		, dataspaceID
		, dataspaceName			

		, is_padded				
		, [allow_page_locks]	
		, [allow_row_locks]		
		, IsAutoStatistics		
		, [ignore_dup_key]		

		, [allocation_unit_id]
		, [allocationUnitType]
		, [allocationUnitTypeDesc]
		, [container_id]			

		, [bufferNumberofPages]
		, [bufferMB]


	)

		SELECT 
				    cteI.object_id
				  , cteI.[index_id]

				, [SchemaName] 
					= schema_name(t.schema_id) 

				, TableName 
					= OBJECT_NAME(ix.OBJECT_ID)

				, IndexName 
					= ix.[name]

				, [indexKeyColumns]
					= cteICK.[columnNames]

				, [indexIncludedColumns]
					= cteICI.[columnNames]

				, [numberofRows]
					= coalesce
						(
							  cteP.[record_count]
							, cteISX.[NumberofRows]
						)


				, [Indexsize(MB)]
					= case
						when ix.[type_desc] in
								( 
									  'CLUSTERED'
									, 'HEAP'
									, 'NONCLUSTERED'
								)
								 then CAST( 8 * (cteP.used_pages)/1024.0 AS DECIMAL(20,1))

						when ix.[type_desc] = 'XML' 
							then CAST( 8 * ( cteISX.[page_count])/1024.0 AS DECIMAL(20,1))

					   end

	
				, ix.is_unique

				, ix.[type_desc]

				, ix.fill_factor

				, ix.is_disabled 

				, ix.data_space_id
				, da.[name]

				, ix.is_padded

				, ix.[allow_page_locks]

				, ix.[allow_row_locks]

				, IsAutoStatistics
					= INDEXPROPERTY
						(
							  ix.object_id
							, ix.[name]
							, 'IsAutoStatistics'
						) 


				, ix.[ignore_dup_key]


			, cteAU.[allocation_unit_id]

			, [allocationUnitType]
				= cteAU.[type]

			, cteAU.[type_desc]

			, cteAU.container_id
			--, cteAU.data_space_id

			, [NumberofPages]
				= isNull
				(
						tblOSBD.[NumberofPages]
					, 0
				)

			, [bufferMB]
				= isNull
				(
					tblOSBD.[bufferMB]
					, 0
				)


		from sys.objects t 

		inner join sys.[schemas] s
				on t.schema_id = s.schema_id
		
		inner join sys.indexes ix 
				on t.object_id=ix.object_id


		inner join cteIndex cteI
				on  ix.object_id = cteI.object_id
				and ix.index_id  = cteI.index_id

		 left outer join sys.data_spaces da 
				on ix.data_space_id = da.data_space_id


		left outer join @tblIndexPhysicalStats cteISX
				on  ix.[object_id] = cteISX.[object_id]
				and ix.index_id = cteISX.index_id
				and @skipXMLIndexes = 0


		--inner join cteIndexColumnsKey cteICK
		left outer join cteIndexColumnsKey cteICK
				on ix.object_id = cteICK.object_id
				and ix.index_id = cteICK.index_id


		left outer join cteIndexColumnsIncluded cteICI
				on ix.object_id = cteICI.object_id
				and ix.index_id = cteICI.index_id

		left outer join ctePartition cteP
				on ix.object_id = cteP.object_id
				and ix.index_id = cteP.index_id
				and ix.data_space_id = cteP.data_space_id


		left outer join cteAllocationUnit cteAU
				on  cteP.[object_id] = cteAU.[object_id]
				and cteP.[index_id]  = cteAU.[index_id]
				and cteP.[data_space_id] = cteAU.[data_space_id]
				and cteP.[type] = cteAU.[type]
				and cteP.[container_id] = cteAU.[container_id]

		left outer join  @tblOSBufferDescriptors  tblOSBD

				on cteAU.[allocation_unit_id] = tblOSBD.[allocation_unit_id]

		left outer join @tblAllocationUnitType tblAUT
				on cteAU.[type_desc] = tblAUT.[Item] COLLATE SQL_Latin1_General_CP1_CI_AS
			

		 /* Index is not a Heap */
		 WHERE [ix].[type] > 0 

		 /* Table is not shipped by MS */
		 and   [t].[is_ms_shipped] = 0  

		 and
				(

						(@skipDisabled = 0)

					or (
								(@skipDisabled = 1)
							and (ix.is_disabled = 0)
						)
				)

		 and  [t].[object_id]
					 = isNull(
								  @objectID
								, [t].[object_id]
							 )

		 OPTION ( MAXDOP 1 )


	/*
		Iterate XML Index list
	*/
	declare @idMax int
	declare @id    int

	declare @objectIDContext int
	declare @indexIDContext  int

	declare @objectNameContext sysname
	declare @indexNameContext  sysname	

	set @id = 1
	set @idMax = ( select count(*) from @tblResult)

	while (@id <= @idMax)
	begin

		/*
			Get Candidate data
		*/
		select
					@objectIDContext  = object_ID
				  ,	@indexIDContext   = index_ID

				  , @objectNameContext = quoteName([schemaName]) 
									     + '.'
										 + quoteName( [TableName])
				  , @indexNameContext  =   [indexName]


		from    @tblResult tblR

		where   tblR.[id] = @id

		/*
			Get Stats
		*/

		exec master.dbo.xp_sprintf
				  @sql output
				, @FORMAT_SQL_DBCC_SHOW_STATISTICS
				, @objectNameContext
				, @indexNameContext

		print '@sql : ' + @sql
					
		delete from @tblIndexStatsDensityVectorStaging

		insert into @tblIndexStatsDensityVectorStaging
		(
			  [density]
			, [averageLength]
			, [columns]	
		)
		exec (@sql)


		insert into @tblIndexStatsDensityVector
		(
			  [object_id]
			, index_id	

		    , [density]
			, [averageLength]
			, [columns]	
		)

		select 
				  @objectIDContext
				, @indexIDContext

				, [density]
				, [averageLength]
				, [columns]	

		from   @tblIndexStatsDensityVectorStaging tblS

		where  tblS.[id]
					=
						(
							select min([id])
							from   @tblIndexStatsDensityVectorStaging tblS_Inner
							where  tblS.[id] = tblS_Inner.[id]
						)



		set @id = @id + 1



	end -- if (@skipXMLIndexes = 0)

	update tblR
	set		  tblR.[density]	   = tblISDV.[density]
			, tblR.[averageLength] = tblISDV.[averageLength] 
			, tblR.[densityColumn] = tblISDV.[Columns]		

	from  @tblResult tblR

	inner join @tblIndexStatsDensityVector tblISDV

			on  tblR.[object_id] = tblISDV.[object_id]
			and tblR.[index_id] = tblISDV.[index_id]


	select 

		  [schemaName]
		, [tableName]
		, [indexName]

		, [indexKeyColumns]
		, [indexIncludedColumns]
		, [Indexsize (MB)]			
			= [Indexsize]
		, [numberofRows]
		, [bufferNumberofPages]
		, [bufferMB]			
		, is_unique				
		, [type_desc]				
		, fill_factor			
		, is_disabled			
		, dataspaceName			
		, is_padded					
		, [allow_row_locks]			
		, IsAutoStatistics			
		, [ignore_dup_key]			

		--, [allocation_unit_id]
		--, [allocationUnitType]
		, [allocationUnitTypeDesc]
		--, [container_id]			

		, [density]	
		, [averageLength]
		, [densityColumn]		

	from   @tblResult tblR


	ORDER BY 
			  is_disabled asc
			, [schemaName]
			, [tableName]
			, [indexName]

			
end
go

exec sys.sp_MS_marksystemobject '[dbo].[sp_helpindexInfo]'
go

 

Sample Invocation

Get indexes for specific table


declare
	  @SchemaName sysname
	, @TableName sysname
	, @IndexName sysname
	, @dataspace sysname

set @schemaName = 'dbo'
set @TableName = 'StudentAttendance'
set @dataspace = null
 exec [dbo].[sp_helpindexInfo] 
 		  @SchemaName = @SchemaName
		, @TableName = @TableName
		, @IndexName = @IndexName
		, @dataspace = @dataspace

 

 

Output:
getInfoForSpecificIndex

Get indexes for specific File group


declare
	  @SchemaName sysname
	, @TableName sysname
	, @IndexName sysname
	, @dataspace sysname

set @schemaName = null
set @TableName = null
set @dataspace = 'Indexes2'
 exec [dbo].[sp_helpindexInfo] 
 		  @SchemaName = @SchemaName
		, @TableName = @TableName
		, @IndexName = @IndexName
		, @dataspace = @dataspace

 

Output:

getInfoForSpecificFilegroup

 

Benefits

  1. Get Index Size
    • SQL Server Engine will choose a smaller index over a bigger one if the indexes being filtered on are satisfied by the smaller index
  2. Identify duplicate Indexes

 

File Repository

  1. GitHub
    Link

 

2 thoughts on “SQL Server – Transact SQL – List Index Info

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s