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

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

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

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 。或在数据库中找不到对应的表！"
    }

}

 

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

妹子后续补上