2

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?

Andrea
  • 11,801
  • 17
  • 65
  • 72
Bernhard Döbler
  • 1,960
  • 2
  • 25
  • 39
  • No, it is not an option. You get the definition of the sequence; it does not "adjust" that definition for current usage. The same thing happens with an identity column. – SMor Jun 14 '18 at 17:28

2 Answers2

2

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:

enter image description here

Andrea
  • 11,801
  • 17
  • 65
  • 72
1

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
user3561900
  • 11
  • 2