Create SQL script that create database and tables

Question

I have a SQL database and tables that I would like to replicate in another SQL Server. I would like to create a SQL script that creates the database and tables in a single script.

I can create "Create" script using the SQL Management Studio for each case (Database and Tables), but I would like to know if combining the both "Create" scripts into single script would be enough.

Thanks.

Answer 1

Although Clayton's answer will get you there (eventually), in SQL2005/2008/R2/2012 you have a far easier option:

Right-click on the Database, select Tasks and then Generate Scripts, which will launch the Script Wizard. This allows you to generate a single script that can recreate the full database including table/indexes & constraints/stored procedures/functions/users/etc. There are a multitude of options that you can configure to customise the output, but most of it is self explanatory.

If you are happy with the default options, you can do the whole job in a matter of seconds.

If you want to recreate the data in the database (as a series of INSERTS) I'd also recommend SSMS Tools Pack (Free for SQL 2008 version, Paid for SQL 2012 version).