将数据库导出为脚本；序列设置不正确 [英] Export Database as Scripts; Sequences not set correctly

问题描述

我想从 SQLServer 2016 中获取数据库并将其设置在客户站点上的 2014 服务器上.

在 SSMS 中，我选择 Tasks => Generate scripts... 并获取包含所有 CREATE TABLE 语句等的 SQL 文件.

我的印象是序列没有正确生成.序列正在使用中并且具有大于 1 的当前值.但是，每个 CREATE SEQUENCE 语句都有一个 START WITH 1 子句.

我可以以某种方式设置序列根据它们的当前值获得一个起始值吗?

解决方案

使用系统表(在本例中为sys.sequences)，您可以生成一个脚本来改变所有序列的当前值.

关于 sys.sequences 系统表的更多信息可以在

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?

解决方案

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:

这篇关于将数据库导出为脚本；序列设置不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！