--有BUG... --@RESTART多0 --可否ALTER TYPE?! declare @restart NUMERIC(28) = 10 , @increment bigint = 100 , @current bigint = 1000 , @go bigint , @cmd nvarchar(max) , @MIN NUMERIC(28) = 1 , @MAX NUMERIC(28) = 999999999999999999999999999 , @CACHE INT = 300 if((@current - @restart) % @increment <> 0) begin select @go = (@current - @restart) / @increment + 1 end else begin select @go = (@current - @restart) / @increment end declare cur_senibal cursor for select concat( 'alter sequence ', QUOTENAME(sch.name), '.', QUOTENAME(seq.name) , char(10), ' RESTART WITH ', @restart , case when @increment is null then '' else char(10) end, case when @increment is null then '' else ' INCREMENT BY ' end, case when @increment is null then '' else @increment end , char(10), ' MINVALUE ', @MIN , char(10), ' MAXVALUE ', @MAX , char(10), ' CACHE ', @CACHE , CHAR(10), CASE @GO WHEN 0 THEN '' ELSE 'DECLARE @I BIGINT = ' + CAST(@GO AS NVARCHAR(MAX)) + CHAR(10) + 'WHILE(@I > 0)' + CHAR(10) + ' BEGIN' + CHAR(10) + ' SELECT NEXT VALUE FOR ' + QUOTENAME(sch.name) + '.' + QUOTENAME(seq.name) + CHAR(10) + ' SELECT @I = @I - 1' + CHAR(10) + ' END' END , char(10)) cmd from sys.sequences seq inner join sys.schemas sch on seq.schema_id = sch.schema_id order by seq.name open cur_senibal fetch next from cur_senibal into @cmd while @@FETCH_STATUS = 0 begin --exec (@cmd) print @cmd fetch next from cur_senibal into @cmd end close cur_senibal deallocate cur_senibal |
sql | SQL Server > sqlsrv | T-SQL >