/****** Object:  UserDefinedFunction [dbo].[fn_CombiningRowsForCDC]    Script Date: 10/12/2009 15:49:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_CombiningRowsForCDC]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_CombiningRowsForCDC]
GO

CREATE FUNCTION [dbo].[fn_CombiningRowsForCDC] 
(
	@schema_name VARCHAR(30),
	@table_name	 VARCHAR(100)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
--This function is used to get a comma separated list for the script.
--Once this has been ran once you can remove it if you like.

	DECLARE @temp AS VARCHAR(MAX)
	DECLARE @cdc_object_id AS INT
	
	SELECT @cdc_object_id = CT.object_id
	FROM cdc.change_tables CT
	INNER JOIN sys.objects O ON CT.source_object_id = O.object_id
	WHERE SCHEMA_NAME(O.schema_id) = @schema_name
	AND OBJECT_NAME(O.object_id) = @table_name

	SELECT @temp = COALESCE(@temp+',','')+[column_name] FROM cdc.captured_columns CC
	WHERE object_id = @cdc_object_id

	RETURN @temp
END
GO