使用PowerShell SQL Server SMO访问特定架构表时遇错误求助

问题重现 #

尝试通过PowerShell的SQL Server SMO对象模型访问指定架构下的表（数据库内存在同名表分布于不同架构），简化脚本如下：

# Input parameters
$ServerName = "MyServer"
$DatabaseName = "MyDatabase"
$SchemaName = "dbo"
$TableName = "MyTable"

# Load the SMO assembly (SQL Server Management Objects)
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$database = $server.Databases[$DatabaseName]
$schema = $database.Schemas[$SchemaName]
$table = $schema.Tables[$TableName]

Write-Host "Completed"

执行到$schema = $database.Schemas[$SchemaName]时报错：

Cannot index into a null array

同时排查发现获取到的schema对象不存在Tables集合。环境为PowerShell 5.1，目标SQL Server 2014/2019，已确认服务器连接成功。

错误原因分析 #

1. SMO组件加载不完整：LoadWithPartialName是已弃用的方法，仅加载Microsoft.SqlServer.Smo.dll无法保证依赖组件（如Microsoft.SqlServer.Management.Sdk.Sfc.dll）被正确加载，导致Schemas/Tables等集合未初始化。
2. 延迟加载未触发刷新：SMO默认采用延迟加载机制，若未主动调用Refresh()方法，Schemas或Tables集合不会从服务器拉取实际数据，表现为null集合或空对象。
3. 未验证对象有效性：未检查$database是否实际存在，若数据库名称错误或权限不足，$database为null，后续访问$database.Schemas必然报错。

修正后的SMO脚本 #

# Input parameters
$ServerName = "MyServer"
$DatabaseName = "MyDatabase"
$SchemaName = "dbo"
$TableName = "MyTable"

# 根据SQL Server版本调整SDK路径：120对应2014，150对应2019
$sdkPath = "C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies"
Add-Type -Path (Join-Path $sdkPath "Microsoft.SqlServer.Smo.dll")
Add-Type -Path (Join-Path $sdkPath "Microsoft.SqlServer.Management.Sdk.Sfc.dll")

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName

# 验证数据库是否存在
$database = $server.Databases[$DatabaseName]
if (-not $database) {
    Write-Error "数据库 [$DatabaseName] 不存在或无访问权限"
    exit 1
}

# 刷新架构集合，确保从服务器获取最新数据
$database.Schemas.Refresh()
$schema = $database.Schemas[$SchemaName]
if (-not $schema) {
    Write-Error "架构 [$SchemaName] 在数据库 [$DatabaseName] 中不存在"
    exit 1
}

# 刷新表集合
$schema.Tables.Refresh()
$table = $schema.Tables[$TableName]

if ($table) {
    Write-Host "成功获取表：$($schema.Name).$($table.Name)"
} else {
    Write-Error "表 [$TableName] 在架构 [$SchemaName] 中不存在"
}

Write-Host "Completed"

替代方案：直接查询系统视图 #

若SMO方式仍存在问题，可通过Invoke-SqlCmd直接查询SQL Server系统视图获取表信息：

$ServerName = "MyServer"
$DatabaseName = "MyDatabase"
$SchemaName = "dbo"
$TableName = "MyTable"

# 使用Windows身份验证，若需SQL身份验证则修改连接字符串
$connectionString = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True"
$query = @"
SELECT t.name AS TableName, s.name AS SchemaName, t.object_id
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = '$SchemaName' AND t.name = '$TableName'
"@

$tableInfo = Invoke-SqlCmd -ConnectionString $connectionString -Query $query
if ($tableInfo) {
    Write-Host "找到目标表：$SchemaName.$TableName"
    # 可根据需求扩展处理表的其他属性
} else {
    Write-Error "未找到指定的表"
}

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