powershell - 如何将每个写入主机导出到 csv？

Question

我有以下脚本显示服务器中每个数据库的连接

我想创建“服务器”、“数据库”和“连接信息”列标题并将其导出到 csv，并且基本上将服务器名称、DBName 和连接信息输出到相应的列。

我调查了PSCustomObject但我不确定如何在我的脚本中完全实现这一点

write-host "`r`n            Server            |               DataBase               |       Connection info      `r`n" -foregroundcolor white -backgroundcolor darkyellow

Import-Module SqlServer

$oldAS = New-Object Microsoft.AnalysisServices.Server
$oldAS.connect("server1")

foreach ($db in $oldAS.Databases){
    $compatibility_level = $db.CompatibilityLevel
    if($compatibility_level -lt 1200)
    {
      $OLDdbName = $db.Name
      Write-Host $OLDdbName -Fore green
[PSCustomObject]@{
    DataBase = $OLDdbName
}
      $db.DataSources | ForEach-Object{write-host "$($_.ConnectionString)";
    [PSCustomObject]@{
        "Connection Info" = $_.ConnectionString
              }
}
    }
    else
    {
        $AS = new-Object Microsoft.AnalysisServices.Tabular.Server
        $AS.Connect("server1")

        foreach ($dbt in $AS.Databases | Where-Object{$compatibility_level -ge 1200} ){
          $dbName = $dbt.Name
          Write-Host $dbName
   [PSCustomObject]@{
        DataBase = $dbName
    }
          if(($dbt.model.datasources[0]).GetType().Name -match "ProviderDataSource") 
          {
            write-host "$($dbt.model.datasources[0].ConnectionString)"
            }
          else {
                #$dbt.model.datasources[0].ConnectionDetails.ToString(); #ToJson
                write-host "$($dbt.model.datasources[0].Credential.ToString())"
   [PSCustomObject]@{
        "Connection Info" = "$($dbt.model.datasources[0].Credential.ToString())"
    }
            }
        }
    }
} | Export-Csv -Path .\CONNECTIONS_LOG.csv -NoTypeInformation -Append

编辑：

Import-Module SqlServer

$oldAS = New-Object Microsoft.AnalysisServices.Server
$oldAS.connect("server1")

#create .net array object
$exportObject = New-Object System.Collections.ArrayList

#create ordered dictionary so column names
#come out in the ordered they were created
$rowObject = [ordered]@{}

foreach ($db in $oldAS.Databases){
    $compatibility_level = $db.CompatibilityLevel
    if($compatibility_level -lt 1200)
    {
      $OLDdbName = $db.Name
      Write-Host $OLDdbName -Fore green
      $db.DataSources | ForEach-Object{
      write-host "$($_.ConnectionString)"

      $rowObject.'Connection' = $($_.ConnectionString)
      $exportObject.Add((New-Object PSObject -Property $rowObject)) | Out-Null
      }

      $rowObject.'Database' = $OLDdbName 
      $exportObject.Add((New-Object PSObject -Property $rowObject)) | Out-Null
    }
    else
    {
        $AS = new-Object Microsoft.AnalysisServices.Tabular.Server
        $AS.Connect("server1")

        foreach ($dbt in $AS.Databases | Where-Object{$compatibility_level -ge 1200} ){
          $dbName = $dbt.Name
          Write-Host $dbName

          $rowObject.'Database' = $dbName
            $exportObject.Add((New-Object PSObject -Property $rowObject)) | Out-Null

          if(($dbt.model.datasources[0]).GetType().Name -match "ProviderDataSource") 
          {
            write-host "$($dbt.model.datasources[0].ConnectionString)"
                $rowObject.'Connection' = $($dbt.model.datasources[0].ConnectionString)
                $exportObject.Add((New-Object PSObject -Property $rowObject)) | Out-Null
            }
          else {
                #$dbt.model.datasources[0].ConnectionDetails.ToString(); #ToJson
                write-host "$($dbt.model.datasources[0].Credential.ToString())"

                $rowObject.'Connection' = $($dbt.model.datasources[0].Credential.ToString()) 
                $exportObject.Add((New-Object PSObject -Property $rowObject)) | Out-Null
            }
        }
    }
}

$exportObject | Export-CSV "C:\temp\dbinfo.csv" -NoTypeInformation -Encoding UTF8 -Delimiter ','

标签： powershell

Answer 1

我无法破译您的脚本在做什么，但这是一个 Powershell 脚本，它使用 sqlps 从我本地计算机上的默认 SQL 实例获取数据库信息。它将每个数据库的信息加载到 PowerShell 对象中，并将该对象添加到 .NET 数组中。然后将 .NET 数组导出到 csv 文件。

我的 SQL Server 名称是 toshiba-bill

cls
Import-Module "sqlps" -DisableNameChecking

#create .net array object
$exportObject = New-Object System.Collections.ArrayList

#create ordered dictionary so column names
#come out in the ordered they were created
$rowObject = [ordered]@{}

#the sqlps dir SQLSERVER command returns 
#Name, Status, Containment Type, Recovery Model, CompatLvl, Collation, and Owner
#for each database in the default instance
$databases = @(dir SQLSERVER:\\SQL\toshiba-bill\Default\Databases) 

foreach ($database in $databases)
{
    $rowObject.'Database name' = $database.Name
    $rowObject.Status = $database.Status
    $rowObject.'Recovery Model' = $database.RecoveryModel
    $exportObject.Add((New-Object PSObject -Property $rowObject)) | Out-Null
}

$exportObject | Export-CSV "C:\temp\dbinfo.csv" -NoTypeInformation -Encoding UTF8 -Delimiter ','

输出