Create SQL scripts that create database and tables

Question

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

I can create a “Create” script using SQL Management Studio for each case (database and tables), but I would like to know if it is enough to combine both “Create” scripts into one script.

Thank.

Answer 1

In SQL Server Management Studio, you can right-click the database you want to replicate and select "Database Script As" so that the tool creates the appropriate SQL file to replicate this database to another server. You can repeat this process for each table that you want to create, and then merge the files into a single SQL file. Remember to add a usage statement after creating the database, but before creating the table.

In later versions of SQL Server, you can get this in a single file in SSMS.

1. Right-click the database.
2. Tasks
3. Generate scripts

This will launch a wizard where you can write a script for the entire database or only parts of it. There seems to be no T-SQL way to do this.

Answer 2

Although Clayton's answer will lead you there (in the end), in SQL2005 / 2008 / R2 / 2012 you have a much simpler option:

Right-click the database, select Tasks and then Generate Scripts , which launches the Script wizard. This allows you to create a single Script that can recreate a complete database, including a table / indexes and constraints / stored procedures / functions / users / etc. There are many options that you can configure to customize the output, but most of them explain themselves.

If you are happy with the default settings, you can do all the work in seconds.

If you want to recreate data in a database (like the INSERTS series), I would also recommend the SSMS Tools Pack (free for SQL Version 2008, Paid for SQL 2012 version).

Answer 3

A great explanation can be found here: Create a script in SQL Server Management Studio

Courtesy of Ali Issa Here's what you need to do:

• Right-click on the database (not the table) and select tasks → generate scripts
• Next → select the requested table / tables (from individual database objects)
• Next → click advanced → data types in script = schema and data

If you want to create a script that only generates tables (without data), you can skip the extended part of the instructions!

Answer 4

I don’t know why SSMS doesn’t take into account the execution order, but it just doesn’t. This is not a problem for small databases, but what if your database has 200 objects? In this case, the order of execution matters because it is not very easy to go through all of these.

For unordered scripts generated by SSMS, you can follow

a) Run the script (some objects will be inserted into some of them, there will be errors)

b) Remove all objects from the script that have been added to the database

c) Go back to a) until everything is done

An alternative is to use a third-party tool, such as ApexSQL Script or any other tools already mentioned in this thread (SSMS toolpack, Red Gate and others).

All of them will take care of addictions for you and save even more time.

Answer 5

Yes, you can add as many SQL statements in one script as you want. Just one note: order matters. You cannot INSERT a table until you CREATE it; you cannot set a foreign key until a primary key is inserted.

Answer 6

Yes, create a database, then create tables, then paste the data, all in one script. Do it all the time