February 6, 2020 at 11:19 am
Hi
i have a .net program that based on the partnumber it creates a sequence (if a sequence exists and if not he creates one).
he creates the sequence with the name "seq_partnumber"
example: seq_345765ab , seq_45123trw
i create a job to reset every sequence at midnight
alter sequence Seq_345765ab
restart with 1
alter sequence Seq_45123trw
restart with 1
alter sequence Seq_98234hjb
restart with 1
what i want to know if it's possible to reset like this
alter sequence Seq_*
restart with 1
or another way that i can automatize the reset of every sequence the program create.
now i have to add manually every sequence that is created
February 6, 2020 at 12:16 pm
No, you can't use wildcards in a DDL statement. Either loop through the sequences and reset them one by one, or write a SELECT statement that generates all the ALTER SEQUENCE commands and execute them all in one go.
John
February 6, 2020 at 12:51 pm
No, you can't use wildcards in a DDL statement. Either loop through the sequences and reset them one by one, or write a SELECT statement that generates all the ALTER SEQUENCE commands and execute them all in one go.
John
might be possible, I've not tried it, but you could have a sequence field and then a persisted computed column that is seq_somevalue
alter table x add 'seq_'+somevalue as myfield persisted - look up the syntax , but you get the gist
you end up with a field that might look like you want.....
MVDBA
February 6, 2020 at 1:28 pm
You could use dynamic SQL.
Query sys.Sequences to get generate the ALTER SEQUENCE SQL statements & then EXEC that.
--Edit: Apologies: just noticed that John already suggested this
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply