Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-08 : 09:06:05
|
| Kulanthaivel writes "How can I delete all the rows in all the tables of a database using a query or other means." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-08 : 09:08:18
|
| You can use:DELETE FROM myTableIf you don't include a WHERE clause or JOIN condition, it will delete all the rows. If it is a big table though, the logging activity can slow it down quite a bit. This is faster:TRUNCATE TABLE myTableTRUNCATE TABLE simply deallocates the data pages for the table, it doesn't actually delete the rows. However, you need to have dbo permissions in order to use it.Check Books Online for more details on these. |
 |
|
|
Spyder
SQLTeam Author
75 Posts |
Posted - 2002-02-10 : 02:58:57
|
quote:
Kulanthaivel writes "How can I delete all the rows in all the tables of a database using a query or other means."
Assuming that your tables do not have foreign key constraints, you could do this to truncate all of the tables of a given database:EXEC sp_msforeachtable "TRUNCATE TABLE ?"If you do have tables with foreign key constraints then you could execute the following:EXEC sp_msforeachtable "DELETE FROM ?"Keep in mind that you may have to execute this one multiple times to ensure all tables are cleaned out -- this is due to dependency chains which require values in child tables to be deleted first before values in parent tables can be deleted.Hope this helps!! |
|
|
|
|
|
|
How to delete all the rows in all the tables in a database