CODEWITHSUNDEEP
sqlsql-servert-sqlsequence
Zart Bitter
Zart Bitter

Reputation: 31

Alter sequence for all sequences to all column with max id

I am trying to create a stored procedure that dynamically restarts all sequences in one specific schema with the max value of 1 column for each table.

Something like this:

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

I tried this:

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

I don't know how to set the @start_value for each table and column.

Upvotes: 3

Views: 4919

Answers (1)

Ashish
Ashish

Reputation: 327

Simple SQL can do the trick

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

Replace table_name and sequence_name with your table/sequence.

Upvotes: 3

Related Questions