CODEWITHSUNDEEP
sql-server-2012
larryq
larryq

Reputation: 16309

Dropping and recreating database, along with tables

I have a database whose structure I'm happy with, but which has a fair amount of dummy data in it. I would like to drop and recreate the database while at the same time wiping out the tables, but retain the table structures and relationships.

When I right-click on the database and choose to script 'drop and create' statements they're for the database itself, but no mention of the tables within-- unless I'm missing something.

Is it possible to generate a script that drops/creates a database and the tables within? I can individually select each table and script out their drop/create statements and order things so it will work, but are there other ways of doing this in one swoop?

Upvotes: 1

Views: 451

Answers (2)

Steve
Steve

Reputation: 216343

I have a localized version of Sql Server 2008 R2, so some of my instructions could be imprecise.
I hope that there are no big differences.

  • Right click on your database and select Tasks and then Generate Scripts
  • Leave the first option selected (Build script for all db and objects) and select next
  • On the second page click Advanced
  • Select the option to Build Script for DROP & CREATE
  • Select the option to Use only the Schema (not Schema and Data)
  • Check the other options you would like to use.
  • Finally choose your save options and click Next until SSMS creates the script

Upvotes: 2

Rahul
Rahul

Reputation: 77926

Instead of that you should do this. That way you can select different tables or stored procedure to script for. See MSDN How to: Generate a Script (SQL Server Management Studio) on how to do it step-by-step.

Right click on DB_Name -> select tasks -> Generate Scripts

enter image description here

Upvotes: 2

Related Questions