Not Able to TRUNCATE all the tables in a database

Question

Hi All
I want to TRUNCATE all tables in SQL Server database, they have FK Constraints.

I have executed the following SP's. First took a list of Constraints using this

SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
USE database_name;
GO
EXEC sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT all’;

I ran the first SQL and everything shows disabled, but when I run the below Query
EXEC sp_MSForEachTable 'TRUNCATE TABLE?'

I still get the same FK Constraint message, please advice, I have over 180 tables.
Thanks

Answer 1

Disabling foreign key constraints is not enough; FK constraints need to be dropped entirely to allow TRUNCATE.

However, you can use 'DELETE' with the FK constraints disabled so you don't need to be concerned with the order of deletion. Don't forget to re-enable the constraints afterwards.

Answer 2

@Dan Guzman
Thanks they all show disable, when I use DELETE instead of TRUNCATE
I get the below error message
Msg 515, Level 16, State 2, Procedure TR_AUDIT_CUST_CUST_PARAM, Line 171
Cannot insert the value NULL into column 'ACTION', table 'Custom_MVPBS71.dbo.AUDIT_TRAIL'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Answer 3

So there is a audit trigger on the table. The fact that it fails when you delete a row is remarkable. I assume that in your case that you want to disable the trigger, but there is all reason to investigate the trigger, or else it may blow up in production on a legit DELETE opersation. (Then again, maybe the reason the trigger fails is that you have already cleared another table.)

You can disable the trigger with ALTER TABLE tbl DISABLE TRIGGER ALL. You will need to enable the trigger when you are done.

Answer 4

Hi @Beacon77
In order to truncate all tables in your database you must first remove all the foreign key constraints, truncate the tables, and then restore all the constraints.

1. Create a table variable to store the constraint drop and creation scripts for the database
2. Load the data for all tables in the database
3. Execute a cursor to drop all constraints
4. Truncate all tables
5. Recreate all the constraints
   Refer to this article for detailed script: Truncate all tables in a SQL Server database

Best regards,
LiHong