之前寫有一篇有關《PowerShell应用之-生成创建表的Transact-SQL脚本》,今早再拿來應用的時候,發現無法按照指定的某一些表來生成腳本。
現在特補充這一功能,先在原來腳本開頭位置,增加一個變量$TablesList:
<
#
===========================================#>
# #生成创建表的脚本V2.0,包含Constraints,Indexes,Triggers
$serverInstance= " PC20\SQL2005DE "
$userName= " sa "
$password= " pc202005 "
$DataBase= " PeripheralDataCollection "
$SrciptOutputPath= " E:\ "
$TablesList= "" # 要生產腳本的表,多表使用逗号“,”分隔
< # ===========================================#>
# #生成创建表的脚本V2.0,包含Constraints,Indexes,Triggers
$serverInstance= " PC20\SQL2005DE "
$userName= " sa "
$password= " pc202005 "
$DataBase= " PeripheralDataCollection "
$SrciptOutputPath= " E:\ "
$TablesList= "" # 要生產腳本的表,多表使用逗号“,”分隔
< # ===========================================#>
再在
#
获得数据库中的用户表
$Tables=((New-Object " Microsoft.SqlServer.Management.Smo.Server " $ServerConnection).databases[ $DataBase]).tables | Where-Object -FilterScript{ $_.IsSystemObject -eq $False}
$Tables=((New-Object " Microsoft.SqlServer.Management.Smo.Server " $ServerConnection).databases[ $DataBase]).tables | Where-Object -FilterScript{ $_.IsSystemObject -eq $False}
增加篩選條件:
#
获得数据库中的用户表
$Tables=((New-Object " Microsoft.SqlServer.Management.Smo.Server " $ServerConnection).databases[ $DataBase]).tables | Where-Object -FilterScript{( $_.IsSystemObject -eq $False) -and ( ( ( $TablesList -split " , ") -contains $_.name) -or ( $TablesList -eq "") ) }
$Tables=((New-Object " Microsoft.SqlServer.Management.Smo.Server " $ServerConnection).databases[ $DataBase]).tables | Where-Object -FilterScript{( $_.IsSystemObject -eq $False) -and ( ( ( $TablesList -split " , ") -contains $_.name) -or ( $TablesList -eq "") ) }
主要增加代碼:
-and ( ( (
$TablesList -split
"
,
")
-contains
$_.name)
-or (
$TablesList
-eq
"") )
其中 ($TablesList -split ",") 應用到表達式"-split"把$TablesList 轉換成列表形式,再通過“-contains”表達式,判斷轉換列表中是否包含有當前返回的table名。
-or ( $TablesList -eq "") 部份只是應用于當$TablesList為空的情況。
生成创建表的脚本V2.0,完整腳本如下:
View Code
<
#
===========================================#>
# #生成创建表的脚本V2.0,包含Constraints,Indexes,Triggers
$serverInstance= " PC20\SQL2005DE "
$userName= " sa "
$password= " pc202005 "
$DataBase= " PeripheralDataCollection "
$SrciptOutputPath= " E:\ "
$TablesList= "" # 要生產腳本的表,多表使用逗号“,”分隔
< # ===========================================#>
[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) -and ( ( ( $TablesList -split " , ") -contains $_.name) -or ( $TablesList -eq "") ) }
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 。或在数据库中找不到对应的表! "
}
}
# #生成创建表的脚本V2.0,包含Constraints,Indexes,Triggers
$serverInstance= " PC20\SQL2005DE "
$userName= " sa "
$password= " pc202005 "
$DataBase= " PeripheralDataCollection "
$SrciptOutputPath= " E:\ "
$TablesList= "" # 要生產腳本的表,多表使用逗号“,”分隔
< # ===========================================#>
[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) -and ( ( ( $TablesList -split " , ") -contains $_.name) -or ( $TablesList -eq "") ) }
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 。或在数据库中找不到对应的表! "
}
}
測試:
(完.)