CODEWITHSUNDEEP
sql-serverdatabase
Amol Patil
Amol Patil

Reputation: 1005

Unable to truncate table - SQL Server

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.

Upvotes: 1

Views: 3715

Answers (2)

StackUser
StackUser

Reputation: 5398

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'

Upvotes: 1

Rahul
Rahul

Reputation: 77856

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.

Upvotes: 5

Related Questions