Run SQL Server Management Studio, right click on the database and select Script Database as > Create to > file
That's for SQL Server 2005. SQL Server 2000 Enterprise Manager has a similar command. Just right-click on the database > All Tasks > Generate Scripts.
EDIT: In SQL Server 2005, you can select "Database" in the object explorer pane and select several databases in the details pane. Then, right-click on your selection and "Script Database as > Create to > file". This will cause it to put them all into one script and it will include all tables, keys, stored procedures, and constraints.
So, I assuming that you cannot install SQL Server Management Studio. Not even the free version. If this is a onetime thing, I would install the red gate tools. The trial version is fully functional for 14 days. Do your work, then uninstall. You might also check out http://www.xsqlsoftware.com/, they have similar functions as Red Gate. If your database is small, then they have a free option for you. I also like http://www.apexsql.com/. Use the trial of one of these and then try again to convince your boss to buy one.
一个更复杂的解决方案是使用类似 Visual Studio Database Edition 的东西（可能太贵了，如果你的评论是可以的），它允许你将每个数据库对象视为项目中的一个节点，同时仍然允许整个事情只需点击几下即可部署。
这两个选项的解释都过于简单化，因为还有很多其他问题——版本控制、数据迁移等——但主要原理是相同的。
一旦您提取了脚本（使用其他答案之一），您可能需要考虑使用此脚本作为“master”的基础。
只需将“设计”保留在数据库之外即可 - 这纯粹是为了“实现”。
尝试将该过程视为类似于开发代码 - 源代码和二进制文件保持独立，后者是从前者生成的。
In addition to the above answers, I'd like to suggest that (for future projects, at least) you don't have you master database design in the database itself.
One way to achieve this is to either simply maintain your database tables, procedures etc as 'CREATE' scripts from day one, and devise a master script that will pull all of the individual scripts together for deployment to a database of your choosing.
A more sophisticated solution is to use something like Visual Studio Database Edition (Probably too pricey, if your comments are anything to go by) which allows you to treat each database object as a node in a project, whilst still allowing the whole thing to be deployed with a few clicks.
The explanation of both of these options is over-simplified, as there are a lot of other issues - versioning, data migration etc - but the main principle is the same.
Once you've extracted your script - using one of the other answers - you may want to consider using this script as the basis for your 'master'.
Just keep the 'design' out of the database - that's purely for 'implementations'.
Try to think of the process as similar to developing code - the source and the binaries are kept separate, with the latter being generated from the former.
我过去曾在 SQL Server 2000 上使用过它。 显然，如果表中有一百万行，那么用脚本编写内容可能不是一个好主意，但实际上它是一个非常简洁的工具，可以制作一系列 SQL 脚本来复制数据库。
I have used this program from CodeProject successfully many times before. Not only does it script out the schema, it can (optionally) script out all the INSERT statements you will need for recreating the database.
I've used it against SQL Server 2000 in the past. Obviously if you have a million rows in a table it might not be a good idea to script out the contents but it's a really neat tool actually to make a series of SQL scripts to replicate a database.
It is 14 days full working version so it will do the entire job for you – it is completely legitimate solution. And you will have all the scripts for the future use with your manual scripting. Anyway I am pretty sure that if you find out how handy the tool is you will buy it in some later stage and that is what they probably hope for by offering fully working trial. It somehow worked in that way in my case.
Just be aware that once the trial expires it affects all the tools so make sure to make the most of it.
Michael Lang，当您右键单击数据库并选择创建脚本时，您需要单击几个选项框才能生成所有内容。 2005 年在这方面要容易得多，但 2000 年也可以做到这一点，你只需要选择适当的选项即可。
Michael Lang, when you right-click on the database and choose to create a script, there are several options boxes that you will need to click in order for everything to be generated. 2005 is much easier in this respect, but 2000 can do this, you just need to select the proper options.
As you mentioned in SQL Server 2000 the command to use is:
While in Enterprise Manager select the database you want to script objects from and then Right Click and select All Tasks -> Generate SQL Scripts...
While in the Options Pane it is handy to select option Create one file per object it that way you can store each object in source control separately.
So then whenever you do some updates on a tableA you can check it out in source control to let others know that you work on it and the after you finish you can script that single object and save check it in.
To script a single object you can you the same option All Tasks -> Generate SQL Scripts... and then select just that one object.
The only problem with that approach is when you want to restore the complete database you need to take care of dependent objects in the sense that the top level object must be restored before the ones dependent on them.
Ironically when you script the whole database to one file the objects are not ordered in terms of dependency but rather based on the creation date. That leads to errors when you try to use it to restore the complete DB.
We ended up creating batch file that would create each object separately calling "osql "
And that all worked pretty well at that time
Now we use SQLCompare Pro and it safes us from or that hassle, but if you do not make release frequently you can live without it.
The others are correct, but in order to create a full database from scratch, you need to create a 'device' in SQL before you run the create tables, and procedures scripts...
Use ADODB, since just about every (if not every) Windows box has it installed to execute the script.
Hell, you could even write a vbScript that executes to build your entire database. Any domain tables you have, you need to remember to turn on the identity insert before you add the items to the DB.
I'm open to source code sharing if you need it. I had to write the very same thing a couple years ago, and ended up with 3 scripts, one that created the device, then the tables, then the procedures/triggers and domain data. I wrote the code to parse the default script and allow the user to name his own database, and logins, etc.. You may not have to go that far.
发布评论
评论（10）
运行 SQL Server Management Studio，右键单击数据库并选择
Script Database as > 创建到> file这是针对 SQL Server 2005 的。SQL Server 2000 企业管理器也有类似的命令。 只需右键单击数据库
>即可 所有任务> 生成脚本。编辑：在 SQL Server 2005 中，您可以在对象资源管理器窗格中选择“数据库”，然后在详细信息窗格中选择多个数据库。 然后，右键单击您的选择并“将数据库脚本编写为 > 创建到 > 文件”。 这将导致它将它们全部放入一个脚本中，并将包括所有表、键、存储过程和约束。
Run SQL Server Management Studio, right click on the database and select
Script Database as > Create to > fileThat's for SQL Server 2005. SQL Server 2000 Enterprise Manager has a similar command. Just right-click on the database
> All Tasks > Generate Scripts.EDIT: In SQL Server 2005, you can select "Database" in the object explorer pane and select several databases in the details pane. Then, right-click on your selection and "Script Database as > Create to > file". This will cause it to put them all into one script and it will include all tables, keys, stored procedures, and constraints.
因此，我假设您无法安装 SQL Server Management Studio。 甚至免费版本也没有。 如果这是一次性的事情，我会安装红门工具。 试用版的功能齐全，有效期为 14 天。 做好你的工作，然后卸载。 您也可以查看http://www.xsqlsoftware.com/，它们的功能与Red Gate类似。 如果您的数据库很小，那么他们可以为您提供免费选项。 我也喜欢 http://www.apexsql.com/。
试用其中一种，然后再次尝试说服您的老板购买其中一种。
So, I assuming that you cannot install SQL Server Management Studio. Not even the free version. If this is a onetime thing, I would install the red gate tools. The trial version is fully functional for 14 days. Do your work, then uninstall. You might also check out http://www.xsqlsoftware.com/, they have similar functions as Red Gate. If your database is small, then they have a free option for you. I also like http://www.apexsql.com/.
Use the trial of one of these and then try again to convince your boss to buy one.
除了上述答案之外，我还想建议（至少对于未来的项目）您不需要掌握数据库本身的数据库设计。
实现这一目标的一种方法是从第一天起就将数据库表、过程等简单地维护为“创建”脚本，并设计一个主脚本，将所有单独的脚本集中在一起以部署到您选择的数据库。
一个更复杂的解决方案是使用类似 Visual Studio Database Edition 的东西（可能太贵了，如果你的评论是可以的），它允许你将每个数据库对象视为项目中的一个节点，同时仍然允许整个事情只需点击几下即可部署。
这两个选项的解释都过于简单化，因为还有很多其他问题——版本控制、数据迁移等——但主要原理是相同的。
一旦您提取了脚本（使用其他答案之一），您可能需要考虑使用此脚本作为“master”的基础。
只需将“设计”保留在数据库之外即可 - 这纯粹是为了“实现”。
尝试将该过程视为类似于开发代码 - 源代码和二进制文件保持独立，后者是从前者生成的。
In addition to the above answers, I'd like to suggest that (for future projects, at least) you don't have you master database design in the database itself.
One way to achieve this is to either simply maintain your database tables, procedures etc as 'CREATE' scripts from day one, and devise a master script that will pull all of the individual scripts together for deployment to a database of your choosing.
A more sophisticated solution is to use something like Visual Studio Database Edition (Probably too pricey, if your comments are anything to go by) which allows you to treat each database object as a node in a project, whilst still allowing the whole thing to be deployed with a few clicks.
The explanation of both of these options is over-simplified, as there are a lot of other issues - versioning, data migration etc - but the main principle is the same.
Once you've extracted your script - using one of the other answers - you may want to consider using this script as the basis for your 'master'.
Just keep the 'design' out of the database - that's purely for 'implementations'.
Try to think of the process as similar to developing code - the source and the binaries are kept separate, with the latter being generated from the former.
我之前曾多次成功使用CodeProject 的这个程序。 它不仅可以编写架构的脚本，还可以（可选）编写重新创建数据库所需的所有 INSERT 语句的脚本。
我过去曾在 SQL Server 2000 上使用过它。 显然，如果表中有一百万行，那么用脚本编写内容可能不是一个好主意，但实际上它是一个非常简洁的工具，可以制作一系列 SQL 脚本来复制数据库。
I have used this program from CodeProject successfully many times before. Not only does it script out the schema, it can (optionally) script out all the INSERT statements you will need for recreating the database.
I've used it against SQL Server 2000 in the past. Obviously if you have a million rows in a table it might not be a good idea to script out the contents but it's a really neat tool actually to make a series of SQL scripts to replicate a database.
如果这是一次性操作并且您不喜欢自己订购对象脚本，只需 下载 RedGate SQL Compare 的免费试用版。
它是 14 天的完整工作版本，因此它将为您完成整个工作 – 这是完全合法的解决方案。 您将拥有所有脚本，供将来手动编写脚本时使用。 不管怎样，我很确定，如果你发现这个工具有多方便，你会在稍后阶段购买它，这可能就是他们希望通过提供全面的试用版来实现的。 在我的例子中，它以某种方式发挥了作用。
请注意，试用期结束后，所有工具都会受到影响，因此请确保充分利用它。
If it is one-off operation and you do not fancy ordering the object scripts yourself just download a free trial version of RedGate SQL Compare.
It is 14 days full working version so it will do the entire job for you – it is completely legitimate solution. And you will have all the scripts for the future use with your manual scripting. Anyway I am pretty sure that if you find out how handy the tool is you will buy it in some later stage and that is what they probably hope for by offering fully working trial. It somehow worked in that way in my case.
Just be aware that once the trial expires it affects all the tools so make sure to make the most of it.
Michael Lang，当您右键单击数据库并选择创建脚本时，您需要单击几个选项框才能生成所有内容。 2005 年在这方面要容易得多，但 2000 年也可以做到这一点，你只需要选择适当的选项即可。
Michael Lang, when you right-click on the database and choose to create a script, there are several options boxes that you will need to click in order for everything to be generated. 2005 is much easier in this respect, but 2000 can do this, you just need to select the proper options.
如果您正在寻找命令行版本，Sql Server Hosting Toolkit 过去曾为我工作过。
If you're looking for a command line version, Sql Server Hosting Toolkit has worked for me in the past.
Microsoft 数据库发布向导（在 Visual Studio 2008 中）。
Microsoft Database Publishing Wizard (in Visual Studio 2008).
正如您在SQL Server 2000中提到的，要使用的命令是：
在企业管理器中选择要从中编写对象脚本的数据库，然后右键单击并选择全部任务-> 生成 SQL 脚本...
在选项窗格中，可以方便地选择选项每个对象创建一个文件，这样您就可以将每个对象存储在源中分别控制。
因此，每当您对 tableA 进行一些更新时，您都可以在源代码管理中签出它，让其他人知道您正在处理它，完成后您可以编写该单个对象的脚本并保存签入。
要编写单个对象的脚本，您可以您可以选择相同的选项所有任务 -> 生成 SQL 脚本...，然后仅选择该一个对象。
该方法的唯一问题是，当您想要恢复整个数据库时，您需要处理依赖对象，因为必须在依赖于它们的对象之前恢复顶级对象。
具有讽刺意味的是，当您将整个数据库编写到一个文件中时，对象并不是根据依赖关系排序的，而是根据创建日期排序的。 当您尝试使用它来恢复完整的数据库时，这会导致错误。
我们最终创建了批处理文件，该文件将分别调用“osql”创建每个对象，
当时一切都运行良好
现在我们使用 SQLCompare Pro 它使我们免受麻烦，但如果您不经常发布，没有它您也可以生活。
As you mentioned in SQL Server 2000 the command to use is:
While in Enterprise Manager select the database you want to script objects from and then Right Click and select All Tasks -> Generate SQL Scripts...
While in the Options Pane it is handy to select option Create one file per object it that way you can store each object in source control separately.
So then whenever you do some updates on a tableA you can check it out in source control to let others know that you work on it and the after you finish you can script that single object and save check it in.
To script a single object you can you the same option All Tasks -> Generate SQL Scripts... and then select just that one object.
The only problem with that approach is when you want to restore the complete database you need to take care of dependent objects in the sense that the top level object must be restored before the ones dependent on them.
Ironically when you script the whole database to one file the objects are not ordered in terms of dependency but rather based on the creation date. That leads to errors when you try to use it to restore the complete DB.
We ended up creating batch file that would create each object separately calling "osql "
And that all worked pretty well at that time
Now we use SQLCompare Pro and it safes us from or that hassle, but if you do not make release frequently you can live without it.
其他人是正确的，但是为了从头开始创建完整的数据库，您需要在运行创建表和过程脚本之前在 SQL 中创建一个“设备”...
使用 ADODB，因为几乎每个（如果不是每个） ) Windows 盒子已安装它来执行脚本。
天哪，您甚至可以编写一个 vbScript 来执行来构建整个数据库。 您拥有的任何域表，您需要记住在将项目添加到数据库之前打开身份插入。
如果您需要的话，我愿意分享源代码。 几年前我不得不写同样的东西，最后得到了 3 个脚本，一个创建设备，然后是表，然后是过程/触发器和域数据。 我编写了代码来解析默认脚本并允许用户命名他自己的数据库和登录名等。您可能不必走那么远。
希望这有帮助。
The others are correct, but in order to create a full database from scratch, you need to create a 'device' in SQL before you run the create tables, and procedures scripts...
Use ADODB, since just about every (if not every) Windows box has it installed to execute the script.
Hell, you could even write a vbScript that executes to build your entire database. Any domain tables you have, you need to remember to turn on the identity insert before you add the items to the DB.
I'm open to source code sharing if you need it. I had to write the very same thing a couple years ago, and ended up with 3 scripts, one that created the device, then the tables, then the procedures/triggers and domain data. I wrote the code to parse the default script and allow the user to name his own database, and logins, etc.. You may not have to go that far.
Hope this helps at all.