Last active
July 24, 2021 13:03
-
-
Save EitanBlumin/316a699e9febe6d02922b3910b3587b2 to your computer and use it in GitHub Desktop.
Change Tracking Utility function and procedure to maintain last copied version
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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