Delete all rows from a SQL Server 2005 database

Question

I want to delete all rows from a SQL Server 2005 database.

Can anyone to define a procedure to delete all rows from database easily?

actually my database exist in server, i want only delete data. — Umair Noor, May 31 '12 at 09:53
possible duplicate of [Delete all rows from database by using single query?](http://stackoverflow.com/questions/10831753/delete-all-rows-from-database-by-using-single-query) — Alex K., May 31 '12 at 10:30

score 1 · Accepted Answer · answered May 31 '12 at 10:43

1

In that case, this will work:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

thankyou for all.

answered May 31 '12 at 10:43

Umair Noor

442
4
17

score 1 · Answer 2 · answered Feb 20 '17 at 21:03

Use this command:

    --Disable Constraints & Triggers
    EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
    --Perform delete operation on all table for cleanup
    EXEC sp_MSforeachtable 'DELETE ?'
    --Enable Constraints & Triggers again
    EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
    --Reset Identity on tables with identity column
    EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1
    BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

score 0 · Answer 3 · answered May 31 '12 at 09:59

0

Maybe this will help:

http://vadivel.blogspot.se/2006/07/easiest-fastest-way-to-delete-all.html

answered May 31 '12 at 09:59

Lasse Edsvik

9,070
16
73
109

Delete all rows from a SQL Server 2005 database

3 Answers3