Disable Foreign key constraint on all tables didn't work ?

Question

i try a lot of commands to disable tables constraints in my database to make truncate to all tables but still now it give me the same error

Cannot truncate table '' because it is being referenced by a FOREIGN KEY constraint.

i try

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_MSforeachtable "TRUNCATE TABLE ?"

and i tried this for each table

ALTER TABLE [Table Name] NOCHECK CONSTRAINT ALL  
truncate table [Table Name]
ALTER TABLE [Table Name] CHECK CONSTRAINT ALL  

and every time i have the previous error message .. could any please help me to solve sucha a problem ?

Answer 1

If you want to truncate the table, probably you have to drop the foreign keys and add them back. From here.

"You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger."

Similar question here in SO.

Answer 2

I think is a rather bad idea. It isn't a good idea to blindly run those sort of SQL commands on ALL tables in your database.

What exactly are you trying to do?

If you want to create a copy of your Database (for example) with the same tables, with no data in them you can do that with SSIS package very easily (just make sure the COPY DATA) option is set to False

http://www.kodyaz.com/articles/transfer-sql-server-objects-task.aspx