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

Create SQL script that create database and tables

提问by tony

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.

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

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.

我可以使用 SQL Management Studio 为每种情况（数据库和表）创建“创建”脚本，但我想知道将两个“创建”脚本合并到单个脚本中是否就足够了。

Thanks.

谢谢。

采纳答案by Clayton

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.

在 SQL Server Management Studio 中，您可以右键单击要复制的数据库，然后选择“脚本数据库为”，让该工具创建适当的 SQL 文件以在另一台服务器上复制该数据库。您可以对要创建的每个表重复此过程，然后将这些文件合并为一个 SQL 文件。不要忘记在创建数据库之后但在创建任何表之前添加 using 语句。

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

在更新版本的 SQL Server 中，您可以在 SSMS 中的一个文件中获取此信息。

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

1. 右键单击一个数据库。
2. 任务
3. 生成脚本

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.

这将启动一个向导，您可以在其中编写整个数据库或部分数据库的脚本。似乎没有这样做的 T-SQL 方式。

回答by CJM

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

尽管 Clayton 的回答会让你（最终）到达那里，但在 SQL2005/2008/R2/2012 中，你有一个更简单的选择：

Right-click on the Database, select Tasksand 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.

右键单击数据库，选择Tasks，然后选择Generate Scripts，这将启动脚本向导。这允许您生成一个脚本，该脚本可以重新创建完整的数据库，包括表/索引和约束/存储过程/函数/用户/等。您可以配置多种选项来自定义输出，但其中大部分都是不言自明的。

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

如果您想重新创建数据库中的数据（作为一系列插入），我还建议使用SSMS 工具包（SQL 2008 版免费，SQL 2012 版付费）。

回答by HelloImKevo

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

可以在这里找到一个很好的解释：在 SQL Server Management Studio 中生成脚本

Courtesy Ali IssaHere's what you have to do:

由 Ali Issa 提供以下是您必须做的：

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

1. 右键单击数据库（不是表）并选择任务 --> 生成脚本
2. 下一步 --> 选择请求的表（从选择特定的数据库对象中）
3. 下一步 --> 点击高级 --> 数据类型到脚本 = 模式和数据

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

如果您想创建一个只生成表（无数据）的脚本，您可以跳过说明的高级部分！

回答by Shawna Jacobs

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.

不知道为什么 SSMS 不考虑执行顺序，但它只是不考虑。这对于小型数据库来说不是问题，但是如果您的数据库有 200 个对象呢？在这种情况下，执行顺序确实很重要，因为要完成所有这些操作并不容易。

For unordered scripts generated by SSMS you can go following

对于 SSMS 生成的无序脚本，您可以遵循

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

a) 执行脚本（有些对象会被插入有些不会，会有一些错误）

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

b) 从脚本中删除所有已添加到数据库的对象

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

c) 回到 a) 直到一切最终都执行完毕

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

替代选项是使用第三方工具，例如ApexSQL Script或此线程中已经提到的任何其他工具（SSMS 工具包、Red Gate 等）。

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

所有这些都将为您处理依赖项，并为您节省更多时间。

回答by duffymo

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.

是的，您可以根据需要将任意数量的 SQL 语句添加到单个脚本中。只需要注意一件事：顺序很重要。在创建表之前，您不能插入表；在插入主键之前，您无法设置外键。