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:
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.
- The second part of the code is for 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:
We can also use the stored procedure with some parameters such as the schema and name table.
We can also query the 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.
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.
Next Steps
- To read more about the views used to create the new and improved sp_helpindex2
- sp_helpindex (Transact-SQL)
- sys.indexes (Transact-SQL)
- sys.partitions (Transact-SQL)
- sys.allocation_units (Transact-SQL)
- Check out these tips:
