Delete all the records

Question

How to delete all the records in SQL Server 2008?

Take a look here: http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql — eulerfx, Mar 18 '11 at 19:17

score 222 · Answer 1 · edited Oct 25 '16 at 21:32

222

To delete all records from a table without deleting the table.

DELETE FROM table_name use with care, there is no undo!

To remove a table

DROP TABLE table_name

edited Oct 25 '16 at 21:32

Dan Watkins

918
9
25

answered Mar 18 '11 at 19:19

PA.

28,486
9
71
95

11

I think it is DELETE FROM table_name (no *) – jzd Mar 18 '11 at 19:34

score 54 · Answer 2 · answered Mar 18 '11 at 19:17

54

from a table?

You can use this if you have no foreign keys to other tables

truncate table TableName

or

delete TableName

if you want all tables

sp_msforeachtable 'delete ?'

answered Mar 18 '11 at 19:17

SQLMenace

132,095
25
206
225

score 19 · Answer 3 · edited Oct 30 '18 at 11:08

19

Use the DELETE statement

Delete From <TableName>

Eg:

Delete from Student;

edited Oct 30 '18 at 11:08

ttarchala

4,277
2
26
36

answered Oct 30 '18 at 09:41

sachind

387
5
8

score 15 · Answer 4 · edited Jan 09 '15 at 21:34

I can see the that the others answers shown above are right, but I'll make your life easy.

I even created an example for you. I added some rows and want delete them.

You have to right click on the table and as shown in the figure Script Table a> Delete to> New query Editor widows:

enter image description here

Then another window will open with a script. Delete the line of "where", because you want to delete all rows. Then click Execute.

enter image description here

To make sure you did it right right click over the table and click in "Select Top 1000 rows". Then you can see that the query is empty.

score 8 · Answer 5 · answered Mar 18 '11 at 19:22

8

If you want to reset your table, you can do

truncate table TableName

truncate needs privileges, and you can't use it if your table has dependents (another tables that have FK of your table,

answered Mar 18 '11 at 19:22

fdaines

1,216
10
12

score 4 · Answer 6 · answered Mar 18 '11 at 19:19

4

For one table

truncate table [table name]

For all tables

EXEC sp_MSforeachtable @command1="truncate table ?"

answered Mar 18 '11 at 19:19

Dumitrescu Bogdan

7,127
2
23
31

score 2 · Answer 7 · answered Jul 28 '21 at 12:50

2

Delete rows in the Results pane if you want to delete records in the database. If you want to delete all of the rows you can use a Delete query.

Delete from Table_name

answered Jul 28 '21 at 12:50

Ihtisham Khattak

170
1
2
11

score 2 · Answer 8 · answered Mar 30 '22 at 12:12

2

delete from TableName

isn't a good practice.

Like in Google BigQuery, it don't let to use delete without "where" clause.

use

truncate table TableName

instead

answered Mar 30 '22 at 12:12

Towsif Ahamed Labib

365
3
14

score 0 · Answer 9 · edited Aug 08 '19 at 20:05

0

When the table is very large, it's better to delete table itself with drop table TableName and recreate it, if one has create table query; rather than deleting records one by one, using delete from statement because that can be time consuming.

edited Aug 08 '19 at 20:05

Samuel Philipp

10,631
12
36
56

answered Aug 08 '19 at 19:35

Shahzod1011

69
1
5

score 0 · Answer 10 · answered May 11 '22 at 15:22

The statement is DELETE FROM YourDatabaseName.SomeTableName; if you are willing to remove all the records with reasonable permission. But you may see errors in the constraints that you defined for your Foreign Keys. So that you need to change your constraints before removing the records otherwise there is a command for MySQL (which may work for others) to ignore the constraints.

SET foreign_key_checks = 0;

Please be aware that this command will disable your foreign keys constrain check, so this can be dangerous for the relationships you created within your schema.

Delete all the records

10 Answers10