PowerShell应用之生成创建表的TransactSQL脚本

开始

---

在上一篇《PowerShell应用之-(SMO) 类库》，我们对SMO类库的应用有了基本的了解，在这里我们将继续对SMO类库进一步了解。在这篇，涉及这几个内容：

• 表对象(Table)
• 脚本选项(ScriptingOptions)
• Sort-Object命令
• Get-Date命令
• Out-File命令

下面我们着重描述表对象和脚本选项两个部分，其他部分写在后面的完整代码中。

表对象(Table)

---

要生成创建表的Transact-SQL脚本，先要找到着手点。在SMO类库中，Microsoft.SqlServer.Management.Smo.Table类中有两个方法,Script()和Script(ScriptingOptions)能为我们提供创建表的Transact-SQL脚本。如果我们要生成一个实例下的一个数据库里面所有表的脚本，我们要借助Microsoft.SqlServer.Management.Smo.Server类下的Databases属性，在Databases属性包含有数据库对象集合。我们接着可以在Databases对象集合中找到Tables属性，它包含有对应数据库的表对象(Table)集合.这一过程就是一层一层的往下检索对应的对象，犹如Tree列表，一层一层的往下搜。

e.g.<代码01>

<#===========================================#>
$serverInstance="WINSERVER01\SQL2008DE01" 
$userName="sa"
$password="sql20081"
$DataBase="test"

<#===========================================#>
[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)
{
    #获得数据库中的用户表
    $Tables=((New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection).databases[$DataBase]).tables | Where-Object -FilterScript{$_.IsSystemObject -eq $False}
    
}

我们在Windows PowerShell ISE上编写上面<代码01>。“获得数据库中用户表”的那行代码我们已作简化，要是一层一层的应用可以这样写：

$Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection
    $DataBases=$Server.Databases
    $Tables=$DataBases.tables  | Where-Object -FilterScript{$_.IsSystemObject -eq $False}

在获取数据库中的用户表代码行中，应用到了Where-Object命令 把系统创建的表给过滤掉。在Table对象中的属性IsSystemObject就是描述是否是系统表。

脚本选项(ScriptingOptions)

---

前面我们提到Microsoft.SqlServer.Management.Smo.Table类中有两个方法,Script()和Script(ScriptingOptions)能生成创建表的脚本。Script()方法是最简单的方法，不过生成的脚本中不包含外键，主键等约束，也没有能生成索引。

为了能把脚本生成得更完整，我们这里调用第二种方法Script(ScriptingOptions),通过设置脚本选项(ScriptingOptions)来生成所需要的脚本。

脚本选项，是ScriptingOptions对象，它对应的类是Microsoft.SqlServer.Management.Smo.ScriptingOptions类。ScriptingOptions对象能为我们提供丰富的对象脚本选项.

e.g.

#腳本选项设置
    $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions"

通过命令“$ScriptingOptions | Get-Member”，可以查到到脚本选项包含的属性，也可以直接执行命令”$ScriptingOptions”查看当前的脚本选项，

e.g.

我们还可以通过设置$ScriptingOptions中的各个属性描述生成脚本的内容格式等。

e.g.

$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

可根据实际需要来设置，这里只是列出部分的内容及设置。

e.g.生成脚本

foreach($t in $Tables)
    {
        $t.Script($ScriptingOptions)
    }

考虑存在外键

上面的生成脚本，执行后直接能在PowerShell控制台显示出来，但我们需要判断外键约束的时候，生成要有先后顺序，要把脚本保存为”.sql”格式文件，而且脚本文件名包含有生成的日期。

为了能对于有外键的表需要注意生成顺序，我们引用了命令Sort-Object把表中的CreateDate和表ID进行排序即可。

e.g.

foreach($t in $Tables  | Sort-Object -Property CreateDate,ID)
    {
        $t.Script($ScriptingOptions)
    }

CreateDate创建日期比较早的排序在前，当创建日期相同，再按表的ID升序排序，这样即可应付存在外键的情况。

完整的代码

---

<#===========================================#>
##生成创建表的脚本，包含Constraints,Indexes,Triggers


$serverInstance="WINSERVER01\SQL2008DE01" 
$userName="sa"
$password="sql20081"
$DataBase="ReplicationDB"
$SrciptOutputPath="E:\"

<#===========================================#>
[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)
        {
            foreach($s In $tb.Script($ScriptingOptions))
            {    
                $i=$Sript.AppendLine($s)
            }
            Write-Host "處理完表 (" $count "/" $Tables.Count ")"  ": " $tb.Name  
            $count+=1       
        }
        
        $i=$Sript.AppendLine("Go")
        
        #输出脚本
        [string]$Path=$SrciptOutputPath+$DataBase+"-"+(Get-Date -format yyyyMMdd)+".sql"
        $Sript.ToString() | Out-File -FilePath $Path            
    }
    Else
    {
        Write-Error "无效的数据库： $DataBase 。或在数据库中找不到对应的表！"
    }

}

小结

---

      上面描述了通过PowerShell 2.0生成创建表的Transact-SQL脚本，包含创建表，约束，外键，默认值，触发器和索引。可以根据自己实际的场景需要，修改部分的脚本选项。当然我在测试上面的脚本中，发现Table.Script(ScriptingOptions)方法生成脚本的方法比较慢，特别是一个数据库中包含有100以上的表，就感觉到缓慢，这方面有待优化提高。