创建创建数据库和表的 SQL 脚本

我有一个 SQL 数据库和表，我想在另一个 SQLServer 中复制。我想创建一个 SQL 脚本，它在一个脚本中创建数据库和表。

我可以使用 SQL Management Studio 为每种情况(数据库和表)创建“创建”脚本，但是我想知道将这两个“创建”脚本组合成单个脚本是否足够。

谢谢。

168599 次浏览
小开

Yes, you can add as many SQL statements into a single script as you wish. Just one thing to note: the order matters. You can't INSERT into a table until you CREATE it; you can't set a foreign key until the primary key is inserted.

小开
最佳答案

In SQL Server Management Studio you can right click on the database you want to replicate, and select "Script Database as" to have the tool create the appropriate SQL file to replicate that database on another server. You can repeat this process for each table you want to create, and then merge the files into a single SQL file. Don't forget to add a using statement after you create your Database but prior to any table creation.

In more recent versions of SQL Server you can get this in one file in SSMS.

  1. Right click a database.
  2. Tasks
  3. Generate Scripts

This will launch a wizard where you can script the entire database or just portions. There does not appear to be a T-SQL way of doing this.

Generate SQL Server Scripts

小开

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).

小开

Not sure why SSMS doesn’t take into account execution order but it just doesn’t. This is not an issue for small databases but what if your database has 200 objects? In that case order of execution does matter because it’s not really easy to go through all of these.

For unordered scripts generated by SSMS you can go following

a) Execute script (some objects will be inserted some wont, there will be some errors)

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

c) Go back to a) until everything is eventually executed

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

All of these will take care of the dependencies for you and save you even more time.

小开

An excellent explanation can be found here: Generate script in SQL Server Management Studio

Courtesy Ali Issa Here's what you have to do:

  1. Right click the database (not the table) and select tasks --> generate scripts
  2. Next --> select the requested table/tables (from select specific database objects)
  3. Next --> click advanced --> types of data to script = schema and data

If you want to create a script that just generates the tables (no data) you can skip the advanced part of the instructions!


提交答案