Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Change Tracking Utility function and procedure to maintain last copied version
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment