How to quickly clear all tables in an SQL Server database from all data?

A quick SQL Server and SQL Server Management Studio (SSMS) tip from the field: I today needed to quickly clear a largish database from all data, so that I can bulk-insert fresh data.

Surely, I could have dropped the database, and re-created its structure, but I wanted to maintain all settings intact, just be able to clear all data. In any larger database, the problem is that you cannot simply start deleting data, because referential integrity constraints (foreign keys) will stop you from doing this manually – and it will take a lot of time to figure out the relationships manually. The same applies to the TRUNCATE TABLE statement.

This is what I did: I want to SQL Server Management Studio, and then opened the database, and opened up the Object Explorer Details window (from the View menu, or by pressing F7). Then, I navigated to the list of tables, and selected all tables in the database.

Next, I right-clicked the tables, and selected the command "Script Table as" and then "DROP and CREATE To", and finally "New Query Editor Window". This created plenty of ALTER TABLE, DROP TABLE and CREATE TABLE statements, but there you have a correctly ordered SQL script that will re-create all tables. This, naturally, removes all data from the original tables as well.

As an additional benefit, you get to reset your IDENTITY field values back to one (1).

Hope this helps! But be sure to run the generated scripts against the correct database... And remember backups!