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

/****** Object:  UserDefinedFunction [dbo].[fn_get_index_columns]    Script Date: 1/20/2015 9:37:57 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create  FUNCTION [dbo].[fn_get_index_columns] 
--{5.5.22.9}
---------------------- 
-- $Change: 175993 $ 
-- $DateTime: 2012/05/10 11:13:24 $ 
-- $Author: darrenr $ 
---------------------- 
(
 	   @pi_table_id  integer,
	   @pi_index_id  integer
)
returns varchar(8000)

AS
  BEGIN
  	declare  @tmp_index_columns VARCHAR(8000), @thiskey VARCHAR(8000);
	--declare  @tmp_index_col sysname    ;
    declare   @i integer;

	   

   

        -- First we'll figure out what the keys are.
         set @i=0
         set @thiskey='';

        select @tmp_index_columns = index_col(object_name(@pi_table_id), @pi_index_id, 1), @i = 2
        if (indexkey_property(@pi_table_id, @pi_index_id, 1, 'isdescending') = 1)
            select @tmp_index_columns = @tmp_index_columns  + ' DESC'

        select @thiskey = index_col(object_name(@pi_table_id), @pi_index_id, @i)
        if ((@thiskey is not null) and (indexkey_property(@pi_table_id, @pi_index_id, @i, 'isdescending') = 1))
            select @thiskey = @thiskey + ' DESC'

        while (@thiskey is not null )
            begin
            select @tmp_index_columns = @tmp_index_columns + ', ' + @thiskey, @i = @i + 1
            select @thiskey = index_col(object_name(@pi_table_id), @pi_index_id, @i)
            if ((@thiskey is not null) and (indexkey_property(@pi_table_id, @pi_index_id, @i, 'isdescending') = 1))
                select @thiskey = @thiskey + ' DESC'
            end
	
		
		set @tmp_index_columns=replace(@tmp_index_columns,',',', ');
        set @tmp_index_columns=replace(@tmp_index_columns,'  ',' ');
		
	   return @tmp_index_columns;
END 

GO
