February 16, 2009 at 9:02 am
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?
February 16, 2009 at 1:00 pm
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...
February 16, 2009 at 1:28 pm
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
February 16, 2009 at 1:35 pm
Thanks. I'll give that a shot.
February 16, 2009 at 2:41 pm
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.
February 17, 2009 at 7:09 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply