SQL Server Delete all data

Is this working for you? I tried this in my DTS task running on SQL Server 2000 SP4:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
truncate table dbo.Sku
truncate table dbo.Orders
truncate table dbo.OrdersOrderItem
truncate table dbo.Inventory
go
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

But I still get the error:
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Cannot truncate table 'dbo.Sku' because it is being referenced by a FOREIGN KEY constraint.

Hi Tim, I understood the ?'s purpose - not sure what you mean - am I misusing the '?' ? They look identical to me :) Unlike your example, I dont want to delete all the data in all tables. I'm doing this for test cases and I have already carefully created some data in other tables that I want to preserve. I just want to shove in some order data for a particular test. But perhaps it doesnt work unless I truncate all the tables? Why would it matter?
Thanks,
Roy

Hi, very good Article!
example with delete works fine, but example with truncating of each table doesn't work properly. I think, that problem is in version of SQL server (and please, correct me if I'am wrong).

When I execute:

SET QUOTED_IDENTIFIER OFF
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
SET QUOTED_IDENTIFIER ON
GO

I'll get error message:
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'dbo.xxxx' because it is being referenced by a FOREIGN KEY constraint.


I have:
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16448
Microsoft .NET Framework 2.0.50727.312
Operating System Vista 6.0.6000

I tried this example of disabling constraints:

http://msdn.microsoft.com/msdnmag/code/Default.aspx?level=root&file=Cursor+to+disable+all+Foreign+Keys.sql&url=http%3a%2f%2fmsdn.microsoft.com%2fmsdnmag%2fissues%2f07%2f04%2fDataPoints%2fdefault.aspx

but with same result :(

similiar discussion of this problem is there:
http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx

This has become easier with SQL 2005:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
GO

EXEC sp_MSForEachTable
'BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
DELETE FROM ?
END CATCH;'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
GO

hi, thank u for posting this but this deleted all my database diagrams please help me with this. i want to clean tables and also keep my database diagrams.thanks