Reset sequences at 00H

  • 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

  • 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

  • John Mitchell-245523 wrote:

    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

  • 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

    • This reply was modified 4 years, 9 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply