PowerShell嵌套ForEach循环问题：BIM文件表列信息匹配异常

问题 #

我编写了一段PowerShell代码，通过ForEach循环从adventureworks.bim文件中提取表名与列信息，但运行结果中所有列的“Table Name”都显示为循环最后一个表“Date”，无法对应各列所属的正确表名。

原代码 #

cls
$BIM = "C:\Users\Desktop\adventureworks.bim"
$origmodel = (Get-Content $BIM -Raw) | Out-String | ConvertFrom-Json
   ForEach($table in $origmodel.Model.tables.name)
   {
     $ColumnProperty = $origmodel.Model.tables.columns | ForEach-Object {
                                                            [pscustomobject] @{
                                                              'Table Name' = $table
                                                              'Object Name' = $_.name
                                                              'DataType' = $_.dataType
                                                            }
                                                        }
                 }
 $ColumnProperty | ConvertTo-Csv -NoTypeInformation

当前错误结果 #

"Table Name", "Object Name", "Datatype"
"Date","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Date","CurrencyKey","int64"
"Date","Currency Code","string"
"Date","CurrencyName","string"
"Date","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Date","CustomerKey","int64"
"Date","GeographyKey","int64"
"Date","Customer Id","string"
"Date","Title","string"
"Date","First Name","string"
"Date","Middle Name","string"
"Date","Last Name","string"
"Date","Name Style","boolean",
"Date","Birth Date","dateTime",
"Date","Marital Status","string"
"Date","Suffix","string"
"Date","Gender","string"
"Date","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Date","DateKey","int64"
"Date","Date","dateTime",
"Date","Day Number Of Week","int64"
"Date","Day Name Of Week","string"
"Date","Day Of Year","int64"
"Date","Week Of Year","int64"
"Date","Month Name","string"

期望结果 #

"Table Name", "Object Name", "DataType"
"Currency","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Currency","CurrencyKey","int64"
"Currency","Currency Code","string"
"Currency","CurrencyName","string"
"Customer","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Customer","CustomerKey","int64"
"Customer","GeographyKey","int64"
"Customer","Customer Id","string"
"Customer","Title","string"
"Customer","First Name","string"
"Customer","Middle Name","string"
"Customer","Last Name","string"
"Customer","Name Style","boolean",
"Customer","Birth Date","dateTime",
"Customer","Marital Status","string"
"Customer","Suffix","string"
"Customer","Gender","string"
"Date","RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61","int64"
"Date","DateKey","int64"
"Date","Date","dateTime",
"Date","Day Number Of Week","int64"
"Date","Day Name Of Week","string"
"Date","Day Of Year","int64"
"Date","Week Of Year","int64"
"Date","Month Name","string"

错误原因及修复方案 #

错误原因 #

1. 原代码遍历的是$origmodel.Model.tables.name，仅获取表名字符串，而非完整表对象，无法关联到该表对应的列。
2. 每次循环中$origmodel.Model.tables.columns会取出所有表的所有列，并将这些列的Table Name设置为当前循环的$table值；同时每次循环都会覆盖$ColumnProperty变量，最终仅保留最后一次循环的结果，导致所有列都显示最后一个表的名称。

修复代码 #

更高效的写法（避免数组频繁拼接）：

cls
$BIM = "C:\Users\Desktop\adventureworks.bim"
$origmodel = Get-Content $BIM -Raw | ConvertFrom-Json

# 遍历完整表对象，而非仅表名
$ColumnProperty = foreach ($table in $origmodel.Model.tables) {
    # 仅处理当前表的列
    $table.columns | ForEach-Object {
        [pscustomobject] @{
            'Table Name'  = $table.name
            'Object Name' = $_.name
            'DataType'    = $_.dataType
        }
    }
}

$ColumnProperty | ConvertTo-Csv -NoTypeInformation

关键修正点 #

• 遍历$origmodel.Model.tables（完整表对象），而非仅表名字符串；
• 针对每个表对象，仅处理其自身的columns集合，确保列与所属表关联；
• 通过foreach循环直接收集输出结果，避免频繁修改数组导致的性能问题。

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