CODEWITHSUNDEEP
sql
Frizosty
Frizosty

Reputation: 19

How to delete all data from tables in a SQL Server 2014 database, but keep all the tables?

As stated I need help deleting all data from every table in a test database. There are 3477 tables and some of the tables were created by a past employee so I was unable to create a schema of the DB and recreate it empty.

Is there a fast way to delete all of the data and keep all of the tables and their structure? Also, I noticed when deleting data from the DB with Delete table_name, that the data file wasn't decreasing in size. Any reason why? Then I tried to just delete the data file to see what would happen and it erased everything, so i had to restore the test database. Now I'm back at block one....

Any help or guidance would be appreciated.... I've read a lot and everything just says use Delete or Truncate, but rather not do that for 3477 tables.

Upvotes: 2

Views: 3930

Answers (1)

Philippe Allard-Latour
Philippe Allard-Latour

Reputation: 11

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

You have a lot of tables (more than 3000...), so take a look to following link to truncate all tables:

Truncate all tables in a SQL Server database

Upvotes: 1

Related Questions