如何用PowerShell获取函数、视图及权限脚本并跨SQL Server同步

获取视图脚本 #

和存储过程的获取逻辑一致，直接调用SMO的Views集合即可：

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("srvMDM")
$db = $srv.Databases.Item("MDM")
# 替换为目标视图名称
$view = $db.Views | Where-Object { $_.Name -eq "YourTargetView" }
# 拼接头部和主体生成完整脚本
$viewScript = $view.ScriptHeader($true) + $view.TextBody

获取函数脚本 #

SQL Server函数分标量函数和表值函数，SMO提供了对应的集合分别处理：

1. 标量函数 #

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("srvMDM")
$db = $srv.Databases.Item("MDM")
# 替换为目标标量函数名称
$scalarFunc = $db.ScalarValuedFunctions | Where-Object { $_.Name -eq "YourScalarFunction" }
$scalarFuncScript = $scalarFunc.ScriptHeader($true) + $scalarFunc.TextBody

2. 表值函数 #

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("srvMDM")
$db = $srv.Databases.Item("MDM")
# 替换为目标表值函数名称
$tableFunc = $db.TableValuedFunctions | Where-Object { $_.Name -eq "YourTableFunction" }
$tableFuncScript = $tableFunc.ScriptHeader($true) + $tableFunc.TextBody

获取对象权限脚本 #

要生成权限脚本，需要通过ScriptingOptions指定包含权限，再调用对象的Script()方法：

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("srvMDM")
$db = $srv.Databases.Item("MDM")

# 配置脚本选项，开启权限包含
$scriptOpts = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$scriptOpts.IncludePermissions = $true

# 示例：获取存储过程的权限脚本
$proc = $db.StoredProcedures | Where-Object { $_.Name -eq "parsing_remains_v1" }
$procPermissionScript = $proc.Script($scriptOpts)

# 视图权限脚本同理
$view = $db.Views | Where-Object { $_.Name -eq "YourTargetView" }
$viewPermissionScript = $view.Script($scriptOpts)

# 函数权限脚本同理
$func = $db.ScalarValuedFunctions | Where-Object { $_.Name -eq "YourScalarFunction" }
$funcPermissionScript = $func.Script($scriptOpts)

批量处理变更对象（结合sys.objects） #

如果要批量处理sys.objects筛选出的变更对象，可以用循环遍历生成脚本：

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("srvMDM")
$db = $srv.Databases.Item("MDM")

# 查询指定时间后变更的存储过程、视图、函数
$changeQuery = @"
SELECT name, type_desc
FROM sys.objects
WHERE modify_date > '2024-01-01' -- 替换为你的变更时间阈值
AND type_desc IN ('SQL_STORED_PROCEDURE', 'VIEW', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')
"@
$changedObjects = $db.ExecuteWithResults($changeQuery).Tables[0]

# 遍历每个对象生成脚本和权限
foreach ($obj in $changedObjects) {
    $objName = $obj.name
    $objType = $obj.type_desc

    # 根据对象类型获取SMO对象
    switch ($objType) {
        "SQL_STORED_PROCEDURE" { $object = $db.StoredProcedures | Where-Object { $_.Name -eq $objName } }
        "VIEW" { $object = $db.Views | Where-Object { $_.Name -eq $objName } }
        "SQL_SCALAR_FUNCTION" { $object = $db.ScalarValuedFunctions | Where-Object { $_.Name -eq $objName } }
        "SQL_TABLE_VALUED_FUNCTION" { $object = $db.TableValuedFunctions | Where-Object { $_.Name -eq $objName } }
    }

    if ($object) {
        # 生成对象定义脚本
        $objScript = $object.ScriptHeader($true) + $object.TextBody
        # 生成权限脚本
        $scriptOpts = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
        $scriptOpts.IncludePermissions = $true
        $permScript = $object.Script($scriptOpts)

        # 可将脚本保存到文件或直接执行到目标服务器
        $objScript | Out-File -Path "C:\Scripts\$objName.sql" -Append
        $permScript | Out-File -Path "C:\Scripts\$objName.sql" -Append
    }
}

内容的提问来源于stack exchange，提问作者MaratKasumov