Displaying Included fields for a NONCLUSTERED Index

By , May 12, 2012 08:58
The standard stored procedure sp_helpindex unfortunately doesn’t display the included fields which can be used with NONCLUSTERED indexes.
I have written a user stored procedure to get around this. I wanted to use a set based solution but couldn’t do so within a reasonable amount of time, so I have reverted to a cursor (something I haven’t written in a log time).
The user stored procedure saves the results of sp_helpindex to a temporary table and the procedes to gather the included fields from the metadata views provided by SQL server.
CREATE PROCEDURE up_helpindex
 @object_name sysname
AS

SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#HelpIndexResults') IS NOT NULL
 DROP TABLE #HelpIndexResults;

CREATE TABLE #HelpIndexResults (
index_name sysname,
index_description VARCHAR(210),
index_keys NVARCHAR(2078),
include_keys NVARCHAR(2078) DEFAULT ''
);

--why reinvent the wheel? - use standard procedures
INSERT INTO #HelpIndexResults (index_name, index_description, index_keys)
 EXECUTE sp_helpindex @object_name;

IF @@ROWCOUNT = 0
 RETURN;

DECLARE @index_name sysname = '';
DECLARE @col_order_reversed INT;
DECLARE @column_nameĀ  sysname;

--now get the INCLUDE keys
DECLARE include_field_cursor CURSOR FOR
SELECT
 ROW_NUMBER() OVER (PARTITION BY ic.index_id ORDER BY ic.index_id, ic.index_column_id DESC),
 i.name AS index_name,
 c.name AS column_name
FROM sys.indexes i INNER JOIN sys.index_columns ic
 ON ic.object_id = i.object_id
 AND ic.index_id = i.index_id
INNER JOIN sys.columns c
 ON c.object_id = ic.object_id
 AND c.column_id = ic.column_id
WHERE ic.is_included_column = 1
ORDER BY ic.index_column_id;

OPEN include_field_cursor;

FETCH NEXT FROM include_field_cursor
 INTO @col_order_reversed, @index_name, @column_name;

WHILE @@FETCH_STATUS = 0
BEGIN
 UPDATE #HelpIndexResults
 SET include_keys += CASE
 WHEN @col_order_reversed > 1 THEN @column_name + ', '
 WHEN @col_order_reversed = 1 THEN @column_name
 END
 WHERE index_name = @index_name;
 FETCH NEXT FROM include_field_cursor
 INTO @col_order_reversed, @index_name, @column_name;
END

CLOSE include_field_cursor;
DEALLOCATE include_field_cursor;

UPDATE #HelpIndexResults
SET include_keys = ''
WHERE include_keys = '';

SELECT index_name, index_description, index_keys, include_keys
FROM #HelpIndexResults
ORDER BY index_name;

SET NOCOUNT OFF;
GO
I would be interested to see any comments about getting this done in a set-base fashion.
Thanks for reading.

One Response to “Displaying Included fields for a NONCLUSTERED Index”

  1. Sean says:

    This could be simplified and greatly improved by using the method described in the following article: http://www.keepitsql.com/2014/05/16/results-from-multiple-rows-in-a-single-column/
    That would lead to a set-based approach eliminating the while-loop used in this code.

Leave a Reply

*

Panorama Theme by Themocracy