0

I have a script that sometimes needs to be run on a fresh database that's just had all the tables created and sometimes needs to be run on an existing database. I have a script to wipe out the tables and update with the latest versions of the reference tables so that This one script can be updated and stored in a source repo to be used on whatever db server needs to run it.

I have a problem with DBCC CHECKIDENT reseeding to use 0 if This is the first run on a fresh database or reseeding to 1 (the desired result) if it is a subsequent run of the same script on the database.

The relevant code at the beginning of the script

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in *ALL* tables (not just refTables)
EXEC sp_MSForEachTable "DELETE FROM ?"
-- reseed the indexes so primary keys start over at 1
declare @cmd varchar(4000)
declare cmds cursor for 
select 'DBCC CHECKIDENT(''' + t.name + ''', RESEED, 0);'
FROM sys.tables AS t
INNER JOIN sys.identity_columns AS i
ON t.object_id = i.object_id

open cmds
while 1=1
begin
    fetch cmds into @cmd
    if @@fetch_status != 0 break
    exec(@cmd)
end
close cmds;
deallocate cmds

How can I get this to behave consistently on initial and subsequent runs so that I don't break foreign keys by having row 1 becoming row 0?

5
  • why delete From X instead of truncate ? why not just let it reseed to the best value with only DBCC CHECKIDENT (table)? Commented Feb 8, 2016 at 20:05
  • 2
    What version of sql server you are using? ALso, why are you using msforeachtable - undocumented and unreliable ?
    – Kin Shah
    Commented Feb 8, 2016 at 20:06
  • truncate has issues with foreign keys I believe was the original reason for not going that route. @Kin 2014
    – lathomas64
    Commented Feb 8, 2016 at 20:12
  • 2
    Seems like a duplicate of this, and particularly check the details in this answer. Commented Feb 8, 2016 at 20:19
  • the second answer there, while not exactly the same did help me fix this. I just needed to add WHERE last_value IS NOT NULL after the object_id line
    – lathomas64
    Commented Feb 8, 2016 at 21:29

1 Answer 1

0
-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in *ALL* tables (not just refTables)
EXEC sp_MSForEachTable "DELETE FROM ?"
-- reseed the indexes so primary keys start over at 1
declare @cmd varchar(4000)
declare cmds cursor for 
select 'DBCC CHECKIDENT(''' + t.name + ''', RESEED, 0);'
FROM sys.tables AS t
INNER JOIN sys.identity_columns AS i
ON t.object_id = i.object_id
WHERE last_value IS NOT NULL

open cmds
while 1=1
begin
    fetch cmds into @cmd
    if @@fetch_status != 0 break
    exec(@cmd)
end
close cmds;
deallocate cmds

adding WHERE last_value IS NOT NULL as suggested by the second answer in the question Aaron Bertrand linked too solved this issue.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.