Export Database as Scripts; Sequences not set correctly

Question

I want to take a Database from a SQLServer 2016 and set it up on a 2014 server on a clients' site.

In SSMS I select Tasks => Generate scripts... and get a SQL file containing all CREATE TABLE statements and the like.

I'm under the impression that the sequences are not generated correctly. The sequences are in use and have current values larger than one. However, every CREATE SEQUENCE statement has a START WITH 1 clause.

Can I set somehow, that the sequences get a start value according to the their cuurenty value?

Answer 1

Using system tables (sys.sequences in this case) you can generate a script that alters the current value of all your sequences.

More info on sys.sequences system table can be found here.

First of all run the following script on your SQL Server 2016 database:

DECLARE @sql NVARCHAR(max)=''

SELECT @sql = @sql + ' ALTER SEQUENCE ' + [name] 
                   + ' RESTART WITH '+ cast ([current_value] AS NVARCHAR(max)) 
                   + CHAR(10) + CHAR(13)    
FROM sys.sequences

PRINT @sql

The output should be a list of ALTER SEQUENCE statements that contain the current values for all your sequences; you can now add this statements at the end of the scripts you generated from SSMS.

For example, in my test DB this is the result of the previous script:

Answer 2

The answer by Andrea is not entirely correct. The current_value of sys.sequences contains the value that was already handed out previously. The sequence should start at the next increment value. Some extra casting is needed because the values are stored as sql_variant.

DECLARE @sql NVARCHAR(max)=''

SELECT @sql = @sql + N' ALTER SEQUENCE ' + s.name + N'.' +sq.name 
                   + N' RESTART WITH '+ cast (cast(sq.current_value as bigint) + cast(sq.increment as bigint) AS NVARCHAR(20))
                   + CHAR(10) + CHAR(13)    
FROM sys.sequences sq
join sys.schemas s on s.schema_id = sq.schema_id

PRINT @sql