Alter sequence for all sequence to all column with max id

Question

我正在尝试创建一个存储过程，它动态地重新启动一个特定模式中的所有序列，每个表的最大值为1列。

如下所示：

alter sequence @schema.@sequence 
   restart with 
     select max(@column) 
     from @table

我试过这个：

SELECT 
    t.name, c.name 
FROM 
    sys.tables t         
INNER JOIN 
    sys.all_columns c  ON c.object_id = t.object_id          
INNER JOIN 
    sys.schemas s ON s.schema_id  = t.schema_id         
WHERE 
    t.name = @table         
    AND c.name = @column        
    AND s.name = @schema  -- to find the table with column the sequence is used

SET @sql_max = 'SELECT MAX(' + @column + ') FROM ' + @table 

--to find the max value to restart the sequence
SET @sql_text = 'ALTER SEQUENCE ' + @schema + '.' + @sequence
          + ' RESTART WITH ' + @start_value
          + ' INCREMENT BY 1
              MINVALUE 0
              MAXVALUE 9223372036854775807
              CACHE  100000;';
-- to restart the sequence with max value I currently selected

我不知道如何为每个表和列设置@start_value。

Answer 1

简单的SQL就可以做到这一点。

GO
DECLARE @seq VARCHAR(MAX);
DECLARE @sql VARCHAR(MAX);
SET @seq = (SELECT MAX(id) FROM table_name) + 1;
SET @sql = 'ALTER SEQUENCE sequence_name RESTART WITH ' + @seq  
EXEC (@sql)
GO

将table_name和sequence_name替换为您的表/序列。