How to Truncate all Tables?

Question

How to Truncate all Tables?

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
February 16, 2009 at 9:02 am

#130949

I've tried several approaches to this including an older one that cursored through the tables to disable all the constraints, truncate all the tables, then enable all the constraints. I've also tried this one which seems simpler and appears to do the same thing without cursors:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
exec sp_MSforeachtable 'TRUNCATE TABLE ?'
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
I still get errors trying to truncate a couple of the tables:
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'dbo.Lessons' because it is being referenced by a FOREIGN KEY constraint.
I ran a query to see if there were any constraints on the DB and I get three constraints left:
Master_TableMaster_ColsChild_TableChild_ColsCol_Order
LessonsLesson_IDLessonDocsDoc_Lesson_ID1
BusinessProcessBusProcIdLessonsLesson_Bus_ID1
TechnologiesTechIdLessonsLesson_Tech_ID1
Why are there still constraints active after I executed a NOCHECK CONSTRAINT ALL on every table? Is there a way to enable truncation of all tables regardless of PK->FK structure?

Viewing 6 posts - 1 through 5 (of 5 total)

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

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 1

Thanks. You'd think people who publish an article titled "How to Truncate All Tables in a Database" would put that little caveat in there...

Lowell SSC Guru Points: 323506 More actions · Answer 2

except for the fact that a delete is logged and truncate is not, doing a DELETE and the DBCC RESEED has the same affect.

this script grabs the tables in FK order and produces the TRUNCATE,DELETE and DBCC reseed statements you might need:

SET NOCOUNT ON

DECLARE

@Level INT,

@MovedToNewLevel INT,

@sql varchar(1024),

@err varchar(125),

@LastBatch int

CREATE TABLE #Hierarchy

(FKLevel INT,

TblName VARCHAR(100),

id Numeric

)

-- Populate the table

INSERT INTO #Hierarchy

select 0 AS 'FKLevel', name as 'TblName',id

FROM sysobjects where xtype='U' and

id not in (select rkeyid from sysforeignkeys) and

id not in (select fkeyid from sysforeignkeys)

INSERT INTO #Hierarchy select 1 AS 'FKLevel', name as 'TblName',id

FROM sysobjects where xtype='U' and

id not in (select id from #Hierarchy) and

id in (select rkeyid from sysforeignkeys) and

id not in (select fkeyid from sysforeignkeys)

INSERT INTO #Hierarchy select 2 AS 'FKLevel', name as 'TblName',id

FROM sysobjects where xtype='U' and

id not in (select id from #Hierarchy) and

id in (select fkeyid from sysforeignkeys)

-- Set the variables

set @Level=2

set @MovedtoNewLevel=1

WHILE @MovedtoNewLevel <> 0

BEGIN

set @LastBatch=@MovedtoNewLevel

set @sql='update #Hierarchy set FKLevel = FKLevel + 1 where FKLevel=' + CAST(@Level as varchar) + ' and'

set @sql=@sql + ' id in (select fkeyid from sysforeignkeys where fkeyid<>rkeyid and'

set @sql=@sql + ' rkeyid in (select id from #Hierarchy where FKLevel=' + CAST(@Level as varchar) + ' ))'

exec(@sql)

SET @MovedtoNewLevel = @@Rowcount

set @err='#ID''s yet to be processed :' + convert(varchar,@MovedtoNewLevel)

--'BANDAID TO WORK AROUND CIRCULAR FK REFERENCES, WHERE EVENTUALLY,

--TABLEA REFERS TO TABLEQ AND TABLEQ REFERS TO TABLEA

if @LastBatch=@MovedtoNewLevel

BEGIN

select top 1 @err= TblName from #Hierarchy WHERE FKLevel IN(SELECT MAX(FKLevel) FROM #Hierarchy )

set @err = 'possible Circular FK''s found, arbitrarily changing ' + @err + ' to break circular reference.'

RAISERROR (@err,1,1)

set rowcount 1

UPDATE #Hierarchy SET FKLevel = FKLevel - 1

WHERE FKLevel IN(SELECT MAX(FKLevel) FROM #Hierarchy )

set rowcount 0

END

--RAISERROR(@ERR,1,1)

SELECT @Level = @Level + 1

End

select 'TRUNCATE TABLE ' + TblName from #Hierarchy where FKLevel = 0

select 'DELETE ' + TblName from #Hierarchy where FKLevel > 0 order by FKLevel

select 'DBCC CHECKIDENT( [' + TblName + '],RESEED,1)' from #Hierarchy where FKLevel > 0 order by FKLevel

drop table #Hierarchy

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 3

rschaeferhig

SSCrazy Eights

Points: 8776

February 16, 2009 at 1:35 pm

#944828

Thanks. I'll give that a shot.

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Alternately, you could generate a schema script and just re-create a blank DB. If this is something you have to do often, you could even take a backup of your blank DB so you could just restore the blank DB instead of re-creating. This would give you the option of including any static data that you did not want to truncate.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

That was a thought however responsibilities and server authorities are split for DBA here and I've had problems taking that approach with other DBs, hence my search for a method that doesn't actually involve dropping and recreating objects.