Sfrgttk

Good for you! in Russian

PTIJ: wiping amalek’s memory?

What's the "normal" opposite of flautando?

In the quantum hamiltonian, why does kinetic energy turn into an operator while potential doesn't?

Single word request: Harming the benefactor

How can I ensure my trip to the UK will not have to be cancelled because of Brexit?

An alternative proof of an application of Hahn-Banach

Motivation for Zeta Function of an Algebraic Variety

Should I take out a loan for a friend to invest on my behalf?

Virginia employer terminated employee and wants signing bonus returned

How many characters using PHB rules does it take to be able to have access to any PHB spell at the start of an adventuring day?

Intuition behind counterexample of Euler's sum of powers conjecture

What does "the touch of the purple" mean?

Can one live in the U.S. and not use a credit card?

Bash script should only kill those instances of another script's that it has launched

Why would one plane in this picture not have gear down yet?

How to secure an aircraft at a transient parking space?

Error during using callback start_page_number in lualatex

What was the Kree's motivation in Captain Marvel?

How can I get players to stop ignoring or overlooking the plot hooks I'm giving them?

Why the color red for the Republican Party

Can Mathematica be used to create an Artistic 3D extrusion from a 2D image and wrap a line pattern around it?

Contract Factories

Definition of Statistic

DBCC CHECKIDENT behaving inconsistently

What could cause the wrong ID to be inserted?Moving tables to another SQL2008 database (including indexes, triggers, etc.)How can I keep two tables in two different databases with different schemas the same in real-time?How dependent are backups on msdb tables like backupset?What is DBCC CHECKIDENT current column valueWhy dropping all foreign keys from a script is not working?Filtering data in a publicationBreaking up long running query into batches runs fine for a while and then produces fatal errorIs there a way to catch a dbcc checkident commandWhat security vulnerabilities are there with IDENTITY_INSERT and DBCC CHECKIDENT

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?

sql-server sql-server-2014 identity

share|improve this question

edited Feb 8 '16 at 20:13

Kin

53.9k481191

asked Feb 8 '16 at 19:57

lathomas64lathomas64

1014

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  why delete From X instead of truncate ? why not just let it reseed to the best value with only DBCC CHECKIDENT (table)?
  
  – Julien Vavasseur
  Feb 8 '16 at 20:05
  
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  What version of sql server you are using? ALso, why are you using msforeachtable - undocumented and unreliable ?
  
  – Kin
  Feb 8 '16 at 20:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  truncate has issues with foreign keys I believe was the original reason for not going that route. @Kin 2014
  
  – lathomas64
  Feb 8 '16 at 20:12
  
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Seems like a duplicate of this, and particularly check the details in this answer.
  
  – Aaron Bertrand♦
  Feb 8 '16 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
  Feb 8 '16 at 21:29
  

  
  
  
  

add a comment | 

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?

sql-server sql-server-2014 identity

share|improve this question

edited Feb 8 '16 at 20:13

Kin

53.9k481191

asked Feb 8 '16 at 19:57

lathomas64lathomas64

1014

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  why delete From X instead of truncate ? why not just let it reseed to the best value with only DBCC CHECKIDENT (table)?
  
  – Julien Vavasseur
  Feb 8 '16 at 20:05
  
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  What version of sql server you are using? ALso, why are you using msforeachtable - undocumented and unreliable ?
  
  – Kin
  Feb 8 '16 at 20:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  truncate has issues with foreign keys I believe was the original reason for not going that route. @Kin 2014
  
  – lathomas64
  Feb 8 '16 at 20:12
  
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Seems like a duplicate of this, and particularly check the details in this answer.
  
  – Aaron Bertrand♦
  Feb 8 '16 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
  Feb 8 '16 at 21:29
  

  
  
  
  

add a comment | 

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?

sql-server sql-server-2014 identity

share|improve this question

edited Feb 8 '16 at 20:13

Kin

53.9k481191

asked Feb 8 '16 at 19:57

lathomas64lathomas64

1014

-- 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

share|improve this question

edited Feb 8 '16 at 20:13

Kin

53.9k481191

asked Feb 8 '16 at 19:57

lathomas64lathomas64

1014

share|improve this question

edited Feb 8 '16 at 20:13

Kin

53.9k481191

asked Feb 8 '16 at 19:57

lathomas64lathomas64

1014

edited Feb 8 '16 at 20:13

Kin

53.9k481191

edited Feb 8 '16 at 20:13

Kin

53.9k481191

asked Feb 8 '16 at 19:57

lathomas64lathomas64

1014

asked Feb 8 '16 at 19:57

lathomas64lathomas64

1014

1 Answer
1

active

oldest

votes

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.

share|improve this answer

answered Feb 8 '16 at 21:30

lathomas64lathomas64

1014

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f128645%2fdbcc-checkident-behaving-inconsistently%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

answered Feb 8 '16 at 21:30

lathomas64lathomas64

1014

add a comment | 

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.

share|improve this answer

answered Feb 8 '16 at 21:30

lathomas64lathomas64

1014

add a comment | 

-- 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.

share|improve this answer

answered Feb 8 '16 at 21:30

lathomas64lathomas64

1014

-- 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

share|improve this answer

answered Feb 8 '16 at 21:30

lathomas64lathomas64

1014

answered Feb 8 '16 at 21:30

lathomas64lathomas64

1014

answered Feb 8 '16 at 21:30

lathomas64lathomas64

1014