SQL Server Index Report with Included Columns, Storage and more for all Tables in a Database

Problem

I am sure sometimes as SQL Server DBAs and Developers we would like to report on more detailed information about indexes on a table.  One of the primary means to find the information is sp_helpindex which is a system stored procedure to check index summary information.  Unfortunately, we do not have important details like Included Columns, Fill Factor, Index Size and more. In this tip I will share a new and improved stored procedure sp_helpindex2, that I have written, to report information in more detail.

Solution

In SQL Server we almost always use sp_helpindex to report information about the indexes on a table, and also the sys.indexes view to get more detailed information like the Fill Factor, Data Space, and more. However, this stored procedure returns just a few important sets of information for only one table at the time.  Here is an example:

sp_helpindex2: New and Improved stored procedure to report information about the indexes on a table

What about if we need more information about included columns in an index?  We can query the sys.index_columns view.  Now what about if we need also to check the index size? We will query the sys.partition and sys.allocation_units views. As you can see now, there are many separate views to query to report important index details.  So we need a faster way to check all necessary index details in a database. Thinking about it, I decided to write a new and improved version of sp_helpindex named sp_helpindex2 which will return more information than sp_helpindex.

sp_helpindex2 Code Explanation

There are four main parts of the sp_helpindex2 code.  Let’s explain more about each part of the code:

  • The first part of the code is for index sizes.

Now we are going to explain more about each part of the content of sp_helpindex2 which has three main parts:

  • The second part of the code is for general index details.
The second part is for getting general index details.
  • The third part of the code is for the index key and included columns details.
  • The final part of the code will join the three results into one report.

sp_helpindex2 Syntax

Now it time to show more details about this new stored procedure sp_helpindex2, first let start with the parameters.

Syntax

sp_helpindex2 [ [ @SchemaName = ] 'SchemaName' ] 
 [ , [ @TableName = ] 'TableName' ],
 [ , [ @IndexName = ] 'IndexName' ],
 [ , [ @dataspace = ] 'dataspace' ]

Arguments

[ @SchemaName = ] 'SchemaName' 
 Name of the schema that the table is contained in, with a default of NULL, which returns all tables regardless schema in the current database.
[ @TableName = ] 'TableName'  
 Name of the table that has the indexes to report, with a default of NULL, which returns all tables and their indexes in the current database
[ @IndexName = ] 'IndexName' 
 Name of the index for which properties are returned, with a default of NULL, which returns all indexes in the current database
[ @dataspace = ] 'dataspace' 
 Name of the data space for this index. Data space is either a filegroup or partition scheme, with a default of NULL, which returns all data spaces that the indexes are contained in.

sp_helpindex2 Usage and Examples

At this time you know the basic syntax and parameters of the stored procedure, now we will show some examples on how to use it.

We can invoke sp_helpindex2 without any parameters to report all details indexes of all tables in the current database. The complete result set includes: SchemaName, TableName, IndexName, IndexKeys, IncludedColumns, Index Size and the properties is_unique, type_desc, data_space, Fill_Factor, IsAutoStatistics, is_disabled, is_padded, allow_page_locks, allow_row_locks, ignore_dup_key.  Here is an example:

At this time you know the basic syntax and parameters of the stored procedure we will show some examples about how to use it.

We can also use the stored procedure with some parameters such as the schema and name table.

We can also use it with some parameters like schema and name table.

We can also query the details for one specific index.

We will query details for one specific index.

We can also query the indexes that are contained within one specific data space (filegroup or partition scheme). For this example I have moved some indexes to a new SECFG filegroup for demonstration purposes.

This is the complete creation script of sp_helpindex2

The sp_helpindex2 Stored Procedure

This is the complete creation script of the sp_helpindex2 stored procedure:

use [master]
go
create proc  dbo.sp_helpindex2 
( @SchemaName sysname=NULL
, @TableName sysname=NULL
, @IndexName sysname=NULL 
, @dataspace sysname=NULL
)
AS
BEGIN
SET NOCOUNT ON
declare @_SchemaName varchar(100)
declare @_TableName varchar(256)
declare @_IndexName varchar(256)
declare @ColumnName varchar(256)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexColumnId int
declare @IsDescendingKey int 
declare @IsIncludedColumn int
-- getting the index sizes
SELECT schema_name(t.schema_id) [SchemaName],
 OBJECT_NAME(ix.OBJECT_ID) AS TableName,
 ix.name AS IndexName,
CAST( 8 * SUM(a.used_pages)/1024.0 AS DECIMAL(20,1))AS 'Indexsize(MB)'
INTO  #IndexSizeTable
from sys.tables t 
inner join sys.indexes ix on t.object_id=ix.object_id
inner join sys.partitions AS p ON p.OBJECT_ID = ix.OBJECT_ID AND p.index_id = ix.index_id
inner join sys.allocation_units AS a ON a.container_id = p.partition_id
 WHERE ix.type>0 and t.is_ms_shipped=0  
 and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)
 GROUP BY schema_name(t.schema_id), ix.OBJECT_ID,ix.name
 ORDER BY OBJECT_NAME(ix.OBJECT_ID),ix.name
 --getting important properties of indexes
select schema_name(t.schema_id) [SchemaName], t.name TableName, ix.name IndexName,
cast( '' as varchar(max)) AS IndexKeys, casT('' as varchar(max)) AS IncludedColumns,
    ix.is_unique
 , ix.type_desc,  ix.fill_factor as [Fill_Factor]
 , ix.is_disabled ,  da.name as data_space,
  ix.is_padded,
     ix.allow_page_locks,
  ix.allow_row_locks,
  INDEXPROPERTY(t.object_id, ix.name, 'IsAutoStatistics') IsAutoStatistics ,
  ix.ignore_dup_key 
 INTO #helpindex
 from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 inner join sys.data_spaces da on da.data_space_id= ix.data_space_id
 where ix.type>0 and t.is_ms_shipped=0  
 and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name)
 and da.name=isnull(@dataspace,da.name) 
 order by schema_name(t.schema_id), t.name, ix.name
---getting the index keys and included columns
declare CursorIndex cursor for
 select schema_name(t.schema_id) [schema_name], t.name, ix.name
    from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 where ix.type>0 and t.is_ms_shipped=0 
 and schema_name(t.schema_id)= isnull(@SchemaName,schema_name(t.schema_id)) and t.name=isnull(@TableName,t.name) AND ix.name=isnull(@IndexName, ix.name) 
 order by schema_name(t.schema_id), t.name, ix.name
open CursorIndex
fetch next from CursorIndex into  @_SchemaName, @_TableName, @_IndexName
while (@@fetch_status=0)
begin
 declare @IndexColumns varchar(4000)
 declare @IncludedColumns varchar(4000)
 set @IndexColumns=''
 set @IncludedColumns=''
 declare CursorIndexColumn cursor for 
  select col.name, ixc.is_descending_key, ixc.is_included_column
  from sys.tables tb 
  inner join sys.indexes ix on tb.object_id=ix.object_id
  inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
  where ix.type>0 and tb.is_ms_shipped=0 
  and schema_name(tb.schema_id)=@_SchemaName and tb.name=@_TableName and ix.name=@_IndexName
  order by ixc.key_ordinal_id
 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn
 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
    set @IndexColumns=@IndexColumns + @ColumnName +', ' 
  else 
   set @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 
     
  fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
 end
 close CursorIndexColumn
 deallocate CursorIndexColumn
 set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
    UPDATE #helpindex 
 SET IndexKeys = @IndexColumns, IncludedColumns=@IncludedColumns
 WHERE [SchemaName]=@_SchemaName and TableName=@_TableName and IndexName=@_IndexName
 
fetch next from CursorIndex into  @_SchemaName, @_TableName, @_IndexName
end
close CursorIndex
deallocate CursorIndex
--showing the results
SELECT hi.SchemaName, hi.TableName, hi.IndexName, hi.IndexKeys, hi.IncludedColumns, ixs.[Indexsize(MB)],
hi.is_unique, hi.type_desc,hi.data_space, hi.Fill_Factor, hi.IsAutoStatistics,
hi.is_disabled, hi.is_padded, hi.allow_page_locks, hi.allow_row_locks,hi.ignore_dup_key
 FROM #helpindex hi
INNER JOIN #IndexSizeTable ixs ON  hi.SchemaName=ixs.SchemaName and hi.TableName=ixs.TableName and hi.IndexName=ixs.IndexName
order by hi.SchemaName, hi.TableName, hi.IndexKeys, hi.IncludedColumns
drop table #helpindex
drop table #IndexSizeTable
set nocount off
end

Mark sp_helpindex2 as a SQL Server System Object

After we have created the sp_helpindex2, then we will mark it as a system object so it can be invoked from any database.

mark sp_helpindex2 as a system object

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *