DROP PROC dbo.new_helpindex
-- This procedures supplements sp_helpindex by providing included columns defined for the index
-- Written by Smitha Reddy
go
CREATE  Procedure dbo.new_helpindex (@table sysname)
AS
SET NOCOUNT ON
SELECT object_name(ic.object_id) AS [object_name], i.name AS index_name, c.name AS Column_Name, 'column usage' = 
		CASE ic.is_included_column
			WHEN 0 then 'KEY'
			ELSE 'INCLUDED' 
        	END,ic.index_id, index_column_id, 0 AS processed
INTO #tmp
FROM sys.index_columns ic 
	INNER JOIN sys.columns  c ON ic.object_id = c.object_id
		AND ic.column_id = c.column_id
	INNER JOIN sys.indexes i ON i.object_id = ic.object_id
		AND i.index_id = ic.index_id
WHERE object_name(ic.object_id) = @table
ORDER BY ic.index_id, index_column_id


CREATE TABLE #tmp2 (idnty int IDENTITY(1,1), object_name sysname, index_name sysname, key_columns varchar(8000), Included_columns varchar(8000))

INSERT INTO #tmp2 (object_name, index_name,key_columns)
SELECT object_name, index_name,Column_Name  
FROM #tmp 
WHERE [Column Usage] = 'KEY' AND index_column_id = 1
ORDER BY index_id, index_column_id

DECLARE @loop1 int, @loop1Max int, 
	@loop2 int, @loop2Max int, 
	@loop3 int, @loop3Max int, 
	@indexname sysname, @AllKeys varchar(8000), @AllIncl varchar(8000), @col sysname

SET @loop1 = 1
SELECT @loop1Max = count(1) from #tmp2
SET @loop2 = 2
SET @loop3 = 1

WHILE @loop1 <= @loop1Max
BEGIN
	SET @AllKeys = ''
	SET @AllIncl = ''
	SET @indexname = (SELECT index_name from #tmp2 WHERE idnty = @loop1)
	SELECT @loop2Max = count(1) FROM #tmp WHERE index_name = @indexname AND [column usage] = 'KEY'

		WHILE @loop2 <= @loop2Max
		BEGIN
			SELECT @AllKeys = @AllKeys+', ' + Column_Name 
			FROM #tmp 
			WHERE index_name = @indexname AND index_column_id = @loop2 AND [column usage] = 'KEY'

			SET @loop2 = @loop2 + 1
		END

	SELECT @loop3Max = count(1) FROM #tmp WHERE index_name = @indexname AND [column usage] = 'INCLUDED'

		WHILE @loop3 <= @loop3Max
		BEGIN
			SET @col = (SELECT Top(1) Column_Name
				    FROM #tmp 
				    WHERE index_name = @indexname AND [column usage] = 'INCLUDED' AND processed = 0 
				    ORDER by index_column_id)
			SET @loop3 = @loop3 + 1
	
			Update #tmp SET processed = 1 WHERE index_name = @indexname AND Column_Name = @col
			SET @AllIncl = @AllIncl + @col +', '
			SET @col = '' 
		END

	UPDATE #tmp2
	SET Key_columns = Key_columns + @AllKeys
	WHERE index_name = @indexname

	IF (len(@AllIncl)>2)
	BEGIN
	UPDATE #tmp2
	SET Included_columns = LEFT( @AllIncl, len(@AllIncl)-1)
	WHERE index_name = @indexname
	END

	SET @loop1 = @loop1 + 1
	SET @loop2 = 2
	SET @loop3 = 1
END

SELECT object_name AS [OBJECT NAME], 
	index_name AS [INDEX NAME], 
	key_columns AS [KEY COLUMNS],	
	Included_columns AS [INCLUDED COLUMNS]
FROM #tmp2
ORDER BY idnty

DROP TABLE #tmp
DROP TABLE #tmp2

RETURN
GO
--new_helpindex 'mytable'