sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | alter all sequence

--有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

Comments