DECLARE @axMinSequence BIGINT
SET @axMinSequence = 0x0000000150000000

DECLARE @nextVal BIGINT       
DECLARE @script AS NVARCHAR(255)
DECLARE @sequenceName AS NVARCHAR(255)
DECLARE @ParmDefinition nvarchar(500)
	
DECLARE @currentStartValue BIGINT
DECLARE @currentNextValue BIGINT
DECLARE @tableId BIGINT
DECLARE @tableName AS NVARCHAR(255)
DECLARE @maxRecIdOut BIGINT
DECLARE @SQLQuery AS NVARCHAR(500)

SET @ParmDefinition = '@maxRecIdOut BIGINT OUTPUT';

DECLARE sqldictcursor CURSOR FOR 
	SELECT dict.TABLEID, dict.SQLNAME
	FROM   SQLDICTIONARY dict            
	WHERE  dict.FIELDID = 0 and dict.FLAGS = 0

OPEN sqldictcursor 
FETCH next FROM sqldictcursor INTO @tableid, @tableName

WHILE @@FETCH_STATUS = 0 
BEGIN           
	SET @sequenceName = 'SEQ_'+ CAST(@tableId AS NVARCHAR);

	IF EXISTS(SELECT * FROM sys.sequences WHERE name = @sequenceName)
	BEGIN										
		SELECT @currentStartValue = CONVERT(BIGINT,start_value), @currentNextValue = CONVERT(BIGINT,current_value) + 1
		FROM sys.sequences WHERE name = @sequenceName
		
		SET @SQLQuery = 'SELECT @maxRecIdOut = ISNULL(MAX(RECID),0) FROM [' + @tableName + ']';
							  
		EXECUTE sp_executesql @SQLQuery, @ParmDefinition, @maxRecIdOut = @maxRecIdOut OUTPUT

		SET @nextVal = @maxRecIdOut + 1

		-- 1st condition handles the upgrade case for CTP7 / CTP8 where-in the SQL sequences started at a much higher range to co-exist
		-- with the existing RECID engine from 6.x.  As of RTW, SQL sequences are exclusively used.
		--
		-- 2nd condition handles cases where the SQL sequences are out-of-date; however, note that we consider equality as out-of-date
		-- to handle the edge case where the current_value is equal to the start_value and indicates that either the start_value or start_value + 1
		-- will be the next value returned by NEXT VALUE FOR [SEQ]
		if ((@currentStartValue > @nextVal) or (@currentNextValue <= @nextVal))
		BEGIN
			if @nextVal < @axMinSequence
				SET @script = 'ALTER SEQUENCE [' + @sequenceName + '] RESTART WITH ' + CAST(@axMinSequence AS NVARCHAR)
			ELSE			
				SET @script = 'ALTER SEQUENCE [' + @sequenceName + '] RESTART WITH ' + CAST(@nextVal AS NVARCHAR)

			--PRINT @script
			EXEC (@script)
		END
	END

	FETCH next FROM sqldictcursor INTO @tableid, @tableName     
END 

CLOSE sqldictcursor
DEALLOCATE sqldictcursor
