转载

疑难杂症--导出对象创建脚本

朋友在折腾导出数据库对象的创建脚本时，遇到一个问题，表结构相同，但不同服务器上导出的脚本却不相同：

一台服务器上的脚本如下：

 SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [dbo].[testb](     [id] [varchar](40) NOT NULL,     [mobile] [varchar](11) NULL,     [name] [varchar](20) NULL,     [address_id] [int] NULL,     [work] [varchar](100) NULL,  CONSTRAINT [PK_testb] PRIMARY KEY CLUSTERED  (     [id] ASC )WITH (     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,      ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY]  GO

另一台服务器上的脚本如下：

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO  CREATE TABLE [dbo].[testb] (   [id] [varchar](40) NOT NULL ,   [mobile] [varchar](11) NULL ,   [name] [varchar](20) NULL ,   [address_id] [int] NULL )ON[PRIMARY]  SET ANSI_PADDING OFF  ALTER TABLE [dbo].[testb] ADD [work] [varchar](100) NULL CONSTRAINT [PK_testb] PRIMARY KEY CLUSTERED  ( [id] ASC )WITH  (     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,      ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  GO SET ANSI_PADDING OFF GO

为什么会这样呢？以前也发现过类似问题，一直认为这两种脚本运行结果都一样，因此没有深入思考。

对比前后代码，会发现除表创建脚本不同外，SET 选项也略有不同，后者有关于ANSI_PADDING的设置，哪究竟是不是这个选项导致的呢？

我们在SSMS工具中进行如下设置：

进入 SSMS>>工具>>选项中，对SET ANSI_PADDING OFF 分别设置为TURE和FALSE，然后依次生成同一对象的脚本，会先后导出的脚本不同。

疑难杂症--导出对象创建脚本

通过上面的选项，我们可以导出不同的对象创建脚本，节省很多人力，如

1>修改"编写索引脚本"为TRUE, 在生成表创建脚本时附带生成索引的创建脚本，

2>修改"检查是否存在对象"为TURE，可以生成带IF NOT EXISTS的检查脚本；

--=================================================================

当然，如果想自动化，PowerShell是最好的选择，下面是摘抄自网络的一段代码，供各位参考：

 <#===========================================#> ##生成创建表的脚本，包含Constraints,Indexes,Triggers ##Link:http://www.cnblogs.com/wghao/archive/2011/11/04/2235220.html  $serverInstance="."  $userName="sa" $password="1234" $DataBase="Test_Sub" $SrciptOutputPath="D:/SC/" $TableList="TB_Lock","TB002"  <#===========================================#> [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null  $ServerConnection =new-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password    Try {     $ServerConnection.Connect() } Catch {     Write-Error $_ }  if($ServerConnection.IsOpen) {         #腳本选项设置     $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions"     $ScriptingOptions.DriAllKeys         = $True     $ScriptingOptions.DriClustered       = $True     $ScriptingOptions.DriAllConstraints  = $True          $ScriptingOptions.DriDefaults        = $True     $ScriptingOptions.DriIndexes         = $True     $ScriptingOptions.DriNonClustered    = $True     $ScriptingOptions.DriPrimaryKey      = $True     $ScriptingOptions.DriUniqueKeys      = $True          $ScriptingOptions.AnsiFile           = $False     $ScriptingOptions.ClusteredIndexes   = $True     $ScriptingOptions.IncludeHeaders     = $False     $ScriptingOptions.Indexes            = $True     $ScriptingOptions.SchemaQualify      = $False     $ScriptingOptions.Triggers           = $True     $ScriptingOptions.XmlIndexes         = $True     $ScriptingOptions.ExtendedProperties = $True     $ScriptingOptions.NoFileGroup        = $True     $ScriptingOptions.NoCollation        = $True     $ScriptingOptions.IncludeIfNotExists = $True     $ScriptingOptions.NoIdentities       = $True      #获得数据库中的用户表     $Tables=((New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection).databases[$DataBase]).tables | Where-Object -FilterScript{$_.IsSystemObject -eq $False}        if($Tables)     {         [System.Text.StringBuilder]$Sript="Use ["+$DataBase+"]`nGo`n"         [int]$count=1                  #刪除腳本         foreach($tb In $Tables | Sort-Object -Property CreateDate,ID -Descending)         {             $i=$Sript.AppendLine("If object_id('[" +$Tb.Name+ "]') Is Not null `n`t  Drop Table ["+ $Tb.Name+ "]")         }                  #创建脚本         foreach($tb In $Tables | Sort-Object -Property CreateDate,ID)         {             if($TableList -contains $tb.Name)             {                 foreach($s In $tb.Script($ScriptingOptions))                 {                         $i=$Sript.AppendLine($s)                 }                                  Write-Host "處理完表 (" $count "/" $Tables.Count ")"  ": " $tb.Name              }             $count+=1                }                  $i=$Sript.AppendLine("Go")                  Write-Host $Sript.ToString()         #输出脚本         [string]$Path=$SrciptOutputPath+$DataBase+"-"+(Get-Date -format yyyyMMdd)+".sql"         $Sript.ToString() | Out-File -FilePath $Path                 }     Else     {         Write-Error "无效的数据库： $DataBase 。或在数据库中找不到对应的表！"     }  }

--===================================================================

妹子后续补上

正文到此结束