Change Tracking Utility function and procedure to maintain last copied version

extended properties change tracking wrapper functions.sql

CREATE FUNCTION dbo.ct_get_last_copied_version
(
	@table_name sysname,
	@target_identifier sysname = null -- optional parameter if you have multiple sync targets
)
RETURNS bigint
AS
BEGIN
RETURN ISNULL((SELECT convert(bigint, [value])
	FROM sys.extended_properties
	WHERE major_id = OBJECT_ID(@table_name)
	AND [name] = N'CT_LastCopiedVersion' + ISNULL(N'_' + @target_identifier, N'')), 0)
END
GO
CREATE PROCEDURE dbo.ct_set_last_copied_version
	@table_name sysname,
	@new_version bigint = 0,
	@target_identifier sysname = null -- optional parameter if you have multiple sync targets
AS
SET NOCOUNT ON;

DECLARE @PropertyName sysname, @PropertyValue sql_variant, @Level0Schema sysname, @Level1Table sysname;

SET @PropertyName = N'CT_LastCopiedVersion' + ISNULL(N'_' + @target_identifier, N'')
SET @Level0Schema = OBJECT_SCHEMA_NAME(OBJECT_ID(@table_name))
SET @Level1Table = OBJECT_NAME(OBJECT_ID(@table_name))
SET @PropertyValue = CONVERT(sql_variant, ISNULL(@new_version, 0))

IF @Level0Schema IS NULL OR @Level1Table IS NULL RAISERROR(N'Specified table "%s" was not found.',16,1,@table_name);

IF NOT EXISTS
(
	SELECT *
	FROM sys.extended_properties
	WHERE major_id = OBJECT_ID(@table_name)
	AND [name] = @PropertyName
)
BEGIN
	EXEC sp_addextendedproperty @name = @PropertyName, @value = @PropertyValue,
		@level0type = N'SCHEMA', @level0name = @Level0Schema,  
		@level1type = N'TABLE',  @level1name = @Level1Table; 
END
ELSE
BEGIN
	EXEC sp_updateextendedproperty @name = @PropertyName, @value = @PropertyValue,
		@level0type = N'SCHEMA', @level0name = @Level0Schema,  
		@level1type = N'TABLE',  @level1name = @Level1Table;
END
GO