/****** Object:  UserDefinedFunction [dbo].[fn_get_included_columns]    Script Date: 1/20/2015 9:34:44 AM ******/
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'fn_get_included_columns') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fn_get_included_columns]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_get_included_columns]    Script Date: 1/20/2015 9:34:44 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create  FUNCTION [dbo].[fn_get_included_columns] 
--{5.7.2.96}
---------------------- 
-- $Change: 186435 $ 
-- $DateTime: 2012/07/31 11:40:26 $ 
-- $Author: builduser $ 
---------------------- 
(
 	   @pi_table_id  integer,
	   @pi_index_id  integer
)
returns varchar(8000)

AS
  BEGIN
	Declare
	   @inc_columns	nvarchar(max),
	   @loop_inc_Count	smallint,
	   @inc_Count		smallint
	   

	   --select @pi_index_id =12
	   --select @pi_table_id= 499532863

	declare @IncludedColumns TABLE 
	(	RowNumber	smallint,
		Name	nvarchar(128)
	)		

		select @inc_columns = NULL
		
		SELECT @inc_Count = count(*)
		FROM sys.tables AS tbl
		INNER JOIN sys.indexes AS si 
			ON (si.index_id > 0 
				and si.is_hypothetical = 0) 
				AND (si.object_id=tbl.object_id)
		INNER JOIN sys.index_columns AS ic 
			ON (ic.column_id > 0 
				and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
				AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
		INNER JOIN sys.columns AS clmns 
			ON clmns.object_id = ic.object_id 
			and clmns.column_id = ic.column_id
		WHERE ic.is_included_column = 1 and
			(si.index_id = @pi_index_id) and 
			(tbl.object_id= @pi_table_id)

		IF @inc_Count > 0
		
		BEGIN
			DELETE FROM @IncludedColumns
			INSERT @IncludedColumns
				SELECT ROW_NUMBER() OVER (ORDER BY ic.index_column_id) 
				, clmns.name 
				FROM
				sys.tables AS tbl
				INNER JOIN sys.indexes AS si 
					ON (si.index_id > 0 
						and si.is_hypothetical = 0) 
						AND (si.object_id=tbl.object_id)
				INNER JOIN sys.index_columns AS ic 
					ON (ic.column_id > 0 
						and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
						AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
				INNER JOIN sys.columns AS clmns 
					ON clmns.object_id = ic.object_id 
					and clmns.column_id = ic.column_id
				WHERE ic.is_included_column = 1 and
					(si.index_id = @pi_index_id) and 
					(tbl.object_id= @pi_table_id)
			
			SELECT @inc_columns = Name FROM @IncludedColumns WHERE RowNumber = 1

			SET @loop_inc_Count = 1

			WHILE @loop_inc_Count < @inc_Count
			BEGIN
				SELECT @inc_columns = @inc_columns + ', ' + Name 
					FROM @IncludedColumns WHERE RowNumber = @loop_inc_Count + 1
				SET @loop_inc_Count = @loop_inc_Count + 1
			END
		END


		
	RETURN @inc_columns 

END

GO


