编写整个数据库 SQL-Server 的脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1162339/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Script entire database SQL-Server
提问by PositiveGuy
Is there a way I can get a scripting of all tables, procs, and other objects from a database? I know there's an option to script the database but it only gave me some sort of top level script, certainly not a script to create all tables, procs, udfs, .etc.
有没有办法从数据库中获取所有表、过程和其他对象的脚本？我知道有一个选项可以为数据库编写脚本，但它只给了我某种顶级脚本，当然不是创建所有表、procs、udfs 等的脚本。
回答by Chris Brandsma
From Management Studio Right-click on your database. Tasks -> Generate Scripts.
从 Management Studio 右键单击您的数据库。任务 -> 生成脚本。
That should do it.
那应该这样做。
回答by Seth Reno
I wrote an open source command line utility named SchemaZenthat does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.
我编写了一个名为SchemaZen 的开源命令行实用程序来执行此操作。它比来自管理工作室的脚本要快得多，而且它的输出对版本控制更友好。它支持对模式和数据进行脚本编写。
To generate scripts run:
要生成脚本，请运行：
schemazen.exe script --server localhost --database db --scriptDir c:\somedir
Then to recreate the database from scripts run:
然后从脚本运行重新创建数据库：
schemazen.exe create --server localhost --database db --scriptDir c:\somedir
回答by devio
回答by si618
We ended up using a combination of SSMS script generation to extract schema and data, and then use our own database tool which allows keyword parsing and token based replacement in scripts. It also ensures scripts are only applied once.
我们最终使用 SSMS 脚本生成的组合来提取模式和数据，然后使用我们自己的数据库工具，该工具允许在脚本中进行关键字解析和基于令牌的替换。它还确保脚本只应用一次。
Why?
为什么？
- We need to support installations on SQL Server 2000, 2005 and 2008, and there are changes to data types between versions, e.g. 2005+ have nvarchar(max), whereas 2000 only supports ntext. So our scripts use a token and based upon the db choice replaces with the correct type.
- Execution of some scripts requires a wait period after execution, e.g. We found if you didn't wait a few seconds after creating a new databases via a script, the SQL Server might fail sometimes (because it hasn't had time to create the db files) when it went on to create tables, etc.
- We wanted to maintain a history of what scripts were executed and when.
- We wanted to allow our Wix MSI installer to specify connection string and credentials, and needed some way to pass these into the scripts, so once again, using tokens and some conditional logic.
- 我们需要支持 SQL Server 2000、2005 和 2008 上的安装，并且版本之间的数据类型有所变化，例如 2005+ 具有 nvarchar(max)，而 2000 仅支持 ntext。因此，我们的脚本使用令牌并根据 db 选择替换为正确的类型。
- 一些脚本的执行需要在执行后等待一段时间，例如我们发现如果通过脚本创建新数据库后没有等待几秒钟，SQL Server 有时可能会失败（因为它没有时间创建数据库文件），当它继续创建表等。
- 我们希望保留有关执行哪些脚本以及何时执行的历史记录。
- 我们希望允许我们的 Wix MSI 安装程序指定连接字符串和凭据，并且需要某种方式将它们传递到脚本中，因此再次使用令牌和一些条件逻辑。
Example script (edited for brevity)
示例脚本（为简洁起见进行了编辑）
-- Sleep: 5
-- Sleep after creating database to allow file system to create db files
CREATE DATABASE [$Database$]
GO
EXEC sp_dbcmptlevel [$Database$], $CompatabilityLevel$
GO
USE [$Database$]
GO
IF '1'!='$IntegratedSecurity$'
BEGIN
CREATE LOGIN [$Login$] WITH PASSWORD=N'$Password$', DEFAULT_DATABASE=[$Database$]
CREATE USER [$User$] FOR LOGIN [$Login$]
EXEC sp_addrolemember N'db_owner', N'$User$'
END
GO
回答by Cameron Forward
Just looking at the table data, to output all of the table data content in Management Studio 2012 and 2014, it is a bit hidden but I found the option after some looking:
只看表数据，要输出Management Studio 2012和2014中的所有表数据内容，它有点隐藏，但我经过一番查找找到了选项：
- Right click the db
- Select 'Tasks' > 'Generate Scripts...'
- On 'Set Scripting Options', click 'Advanced'
- Under 'General', set 'Types of data to script' to true (it is at the bottom of the 'General' group)
- 右键单击数据库
- 选择“任务”>“生成脚本...”
- 在“设置脚本选项”上，单击“高级”
- 在“General”下，将“Types of data to script”设置为 true（位于“General”组的底部）
回答by Barry Hurt
I recommend looking at RedGate SQL packager. It is not free, but has been useful enough to be worth the price.
我建议查看 RedGate SQL 打包器。它不是免费的，但已经足够有用，值得付出代价。
回答by polyglot
If you need to do it programmatically, you can use the SQL DMO library (OLE) against SQL Server 2000, but more likely you may wish to use the SQL SMO library (native .NET libraries) against SQL Server 2005 and later.
如果您需要以编程方式执行此操作，您可以对 SQL Server 2000 使用 SQL DMO 库 (OLE)，但更有可能的是，您可能希望对 SQL Server 2005 及更高版本使用 SQL SMO 库（本机 .NET 库）。
Both these libraries are integral to the SQL Server administrative tools install.
这两个库都是 SQL Server 管理工具安装的组成部分。
This is in the case that generating the full database script from SQL Server Management Studio is insufficient.
这是在从 SQL Server Management Studio 生成完整数据库脚本不足的情况下。
