Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2015-02-03   |   Comments (3)   |   Related Tips: More > Indexing

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)[email protected]_SchemaName and [email protected]_TableName and [email protected]_IndexName
  order by ixc.index_column_id

 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn
 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
    set @[email protected] + @ColumnName +', ' 
  else 
   set @[email protected]  + @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, [email protected]
 WHERE [SchemaName][email protected]_SchemaName and [email protected]_TableName and [email protected]_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


Last Updated: 2015-02-03


get scripts

next tip button



About the author
MSSQLTips author Percy Reyes Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 11, 2016 - 8:55:26 AM - Norman Back To Top

suggestion: use insensitive and for read only cursors.


Wednesday, February 24, 2016 - 9:45:58 AM - darlove Back To Top

Thanks for this. Very useful and informative. However, one question. Can this not be written without the horrible use of a cursor? And if not (but I believe this not to be the case), why is the cursor so badly specified? It should be read-only, forward and static to ensure the least impact on the server...


Wednesday, February 11, 2015 - 8:25:24 AM - David Koth Back To Top

A nice addition I did for this was ot add in the presence of a filter and the filter definition.


Learn more about SQL Server tools