Unable to truncate table - SQL Server

Question

I get the following error.

Could not drop object 'tablename' because it is referenced by a FOREIGN KEY constraint.

This means there are references for the table I want to truncate. Then I use to remove all constraint for all table using following queries.

use mydb

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

truncate table mytable.

But it's not working, please assist.

That already answer before http://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint — mfathy00, Jan 05 '16 at 06:52
You mention you want to **truncate** your table, but the error says you cannot **drop** the table...... what now? Truncate or drop ?? — marc_s, Jan 05 '16 at 08:35
And `NOCHECK CONSTRAINT ALL` does *not* remove or disable constraints. What are you actually trying to do and what code did you use? The text of the question contradicts your code. What is the *actual* problem you are trying to fix by using truncate and removing constraints? — Panagiotis Kanavos, Jan 05 '16 at 08:42
@PanagiotisKanavos I tried both truncate and drop but both not working. — Amol Patil, Jan 05 '16 at 12:16

score 5 · Accepted Answer · answered Jan 05 '16 at 06:55

NO, don't use sp_MSforeachtable and it's not documented or supported. Rather, run command sp_help mytable or sp_helptext mytable and see which all table referencing table mytable. Drop the FK constraint for moment and then run your TRUNCATE command and other processing.

Once everything is fine, re-create the FK constraint again.

score 1 · Answer 2 · answered Jan 05 '16 at 07:43

Try this but you should not use this in Production environment. This code will truncate all the tables in a specific database.

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
exec sp_MSforeachtable 'DELETE FROM ?'  
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 
exec sp_MSforeachtable 'IF NOT EXISTS (SELECT *
    FROM SYS.IDENTITY_COLUMNS
    JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
    WHERE SYS.TABLES.Object_ID = OBJECT_ID(''?'') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL)
    AND OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1
    DBCC CHECKIDENT (''?'', RESEED, 0) WITH NO_INFOMSGS'

Unable to truncate table - SQL Server

2 Answers2