如何编写SQL Server中的索引，键，外键脚本

I would like to get the details of all indexes, keys, and foreign keys from a database in SQL Server (2008). How do I do this?

我想从SQL Server（2008）中的数据库中获取所有索引，键和外键的详细信息。我该怎么做呢？

I plan to use this to synchronize those properties across a couple of somewhat similar databases.

我计划使用它来在几个有点类似的数据库中同步这些属性。

I can use SQL Server Management Studio, but I cannot do a full backup of a database because of restrictions set by the web hoster.

我可以使用SQL Server Management Studio，但由于Web主机设置的限制，我无法对数据库进行完整备份。

Secondary question that you do not need to answer:

您不需要回答的次要问题：

Why can't there be something similar to the database schema in Mysql that simply lists all of the database structure in text SQL script format?

为什么不能在Mysql中出现类似于数据库模式的东西，它只是以文本SQL脚本格式列出所有数据库结构？

6 个解决方案

#1

Assuming you are using atleast SQL Server 2005 or above, you can use the Database Publishing Wizard to script your schema

假设您使用的是至少SQL Server 2005或更高版本，则可以使用数据库发布向导为您的架构编写脚本

This can be used to generate scripts for schema only, data or both.

这可用于为仅模式，数据或两者生成脚本。

It integrates directly into Visual Studio 2005 and/or Visual Web Developer 2005

它直接集成到Visual Studio 2005和/或Visual Web Developer 2005中

If you are using VS2008, v1.2 version of SQL Publishing Wizard comes pre-installed. You can check out here where to invoke it from.

如果您使用的是VS2008，则会预安装v1.2版本的SQL发布向导。你可以在这里查看从哪里调用它。

#2

If you are looking for granular, more specific objects to script you can also use generate scripts from the respective DB's Task > Generate Scripts option.

如果要查找用于脚本的精细，更具体的对象，还可以使用相应DB的“任务”>“生成脚本”选项中的生成脚本。

Check http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx for details.

有关详细信息，请访问http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx。

#3

How to find foreign key dependencies in SQL Server?

如何在SQL Server中查找外键依赖项？

#4

If you need to get script from T-SQL then only using xp_cmdshell. For example scripting concreate index of concreate view with SMO and powershell (result is in @script variable, you could execute it with sp_executesql ):

如果你需要从T-SQL获取脚本，那么只使用xp_cmdshell。例如，脚本使用SMO和powershell创建concreate视图的索引（结果在@script变量中，您可以使用sp_executesql执行它）：

DECLARE @OUTPUT TABLE (line nvarchar(max))
DECLARE @cmd VARCHAR(8000), @ps VARCHAR(8000), @psLoadAssemblies VARCHAR(8000), @script nvarchar(max) =''
DECLARE @srv nvarchar(max)='<server name>', 
        @ln nvarchar(max)='<login>', 
        @pw nvarchar(max)='<password>', 
        @db nvarchar(max) = '<database>', 
        @schemaName nvarchar(max) = '<schema>',  -- without '[' ']'
        @viewName nvarchar(max) = '<view name>',  -- without '[' ']'
        @indexName nvarchar(max) = '<index name>' -- without '[' ']'

SET @psLoadAssemblies  = '[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'')|Out-Null;'
SET @ps='$using=''Microsoft.SqlServer.Management.Smo'';$s=new-object($using+''.Server'') $srv;$c = $s.ConnectionContext;$c.LoginSecure=$false;$c.Login=$ln;$c.Password=$pw; Write-Host ($s.Databases[$db].Views.Item($viewName,$schemaName).Indexes[$indexName].Script())'
SET @ps=REPLACE(@ps,'$srv',''''+@srv+'''')
SET @ps=REPLACE(@ps,'$ln',''''+@ln+'''')
SET @ps=REPLACE(@ps,'$pw',''''+@pw+'''')
SET @ps=REPLACE(@ps,'$db',''''+@db+'''')

SET @ps=REPLACE(@ps,'$schemaName',''''+@schemaName+'''')
SET @ps=REPLACE(@ps,'$viewName',''''+@viewName+'''')
SET @ps=REPLACE(@ps,'$indexName',''''+@indexName+'''')

SET @cmd = 'powershell -Command "'+@psLoadAssemblies+@ps+'"'
exec dev.Msg @cmd
INSERT INTO @OUTPUT
exec xp_cmdshell @cmd

SELECT @script+line FROM @OUTPUT
WHERE line is not null

PRINT @script

P.S. For those who asks why we could need such tricks: in some scenarios, e.g. "import data using third party tool", the approach "drop-recreate" works better than "enable-disable" objects, e.g. because such third party tool can call "truncate" and if your table participates in schema-bound view you will get a third party tool error (truncating table participating in indexed views throws an error, therefore we are forced to drop the view with all indexes before import and recreate it after).

附：对于那些问我们为什么需要这些技巧的人：在某些情况下，例如： “使用第三方工具导入数据”，“drop-recreate”方法比“enable-disable”对象更好地工作，例如因为这样的第三方工具可以调用“truncate”，如果你的表参与模式绑定视图，你将得到第三方工具错误（截断表参与索引视图会引发错误，因此我们*删除所有索引的视图在导入之前并在之后重新创建它）。

#5

As an alternative to InSane's perfect answer, you can right click any object in SSMS to script it to a text file or a window.
A few free and non-free products also allow you to this, including WinSQL.

作为InSane完美答案的替代方案，您可以右键单击SSMS中的任何对象，将其编写为文本文件或窗口。一些免费和非免费的产品也允许你这样做，包括WinSQL。

#6

I have seen the comment or reply of the user "dontomoso". After translating to English it seems that the "Operation is not valid due to the current state of the object. (SqlPubWiz)" in "database publishing wizard" is the error experienced.

我看过用户“dontomoso”的评论或回复。转换为英语后，“数据库发布向导”中的“操作无效，因为对象的当前状态。（SqlPubWiz）”是错误。

After so many experiments and trials, in this app. the database name is case sensitive. Put correct case value for the -d parameter. The solution is simple, change the default schema name or Initial Catalog name same as "used while Create DB". E.g. While creating if playGround is used, use playGround here too...Playground or playground or PlayGround shall generate this error.

经过这么多实验和试验，在这个应用程序中。数据库名称区分大小写。为-d参数添加正确的大小写值。解决方案很简单，更改默认架构名称或初始目录名称与“在创建DB时使用”相同。例如。在创建使用playGround的同时，也可以在这里使用playGround ... Playground或playground或PlayGround会产生此错误。

I hope this help!

我希望这有帮助！

#1