How to avoid Foreign Keys constraints for all tables in DB truncate ?

Question

Hi, for designing purposes i need to truncate all DB which has lots of FK's. I cannot use DELETE command simply because some tables set with Identity of TinyInts and contain about 150 items.

this is a query ( truncate all tables in selected DB ) i'm trying to run

Declare @t varchar (1024)
Declare tbl_cur cursor for  
select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tbl_cur

FETCH NEXT  from tbl_cur INTO @t

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('TRUNCATE TABLE '+ @t)
FETCH NEXT  from tbl_cur INTO @t
END

CLOSE tbl_cur
DEALLOCATE tbl_Cur

What the best and easiest way to achieve truncate on DB with many FK's ?

Answer 1

If you're trying to delete all rows in all related tables, can you drop the constraints, truncate the tables, then create the constraints again?

Or...this could be useful as well. Looks like it loops through from the bottom of the dependency tree up until everything's gone.

Answer 2

You can disable all foreign keys, truncate tables, then enable the foreign keys back.

Disable: ALTER TABLE table_name NOCHECK CONSTRAINT ALL

Enable: ALTER TABLE table_name CHECK CONSTRAINT ALL

Answer 3

If the reason you are avoiding delete is simply in order to avoid the issue with identity columns you can just delete then use

DBCC CHECKIDENT('TableName', RESEED, 0)

to reset them to zero.