Script for running the same query against multiple MySQL databases on the same server and saving that result to a .csv.

MultiQuery.ps1

# REQUIREMENTS:
# 1. The MySql connector must be installed and the path properly configured on line 24

param(
   [string]$query,
   [int]$limit,
   [int]$db0x
)

if (!$query) {
   Write-Warning 'Usage: MultiQuery.ps1 -query "select count(*) as customerCount from {db}.customers" [-limit 50] [-db0x 1]'
   Write-Warning 'Results will be written to ~\Desktop\Multiquery.csv'
   exit 0
}

if (!$query.Contains("{db}")) {
   Write-Warning 'Error: Query parameter must contain placeholder for database name: {db}'
   exit 0
}

if (!$limit) {
   $limit = 50
}

if (!$db0x) {
   $db0x = 1
}

[void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 6.6.4\Assemblies\v4.0\MySql.Data.dll")

$databaseNameSubstring = "%%_rm12"
$Mysqlhost = '[dbhost]' + $db0x
$Mysqluser = '[dbuser]'
$Mysqlpass = '[dbpassword]'


$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString = "server=$Mysqlhost;uid=$Mysqluser;pwd=$Mysqlpass;database=information_schema" }
$Connection.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
$sql.CommandText = "SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE '$databaseNameSubstring'"

$myreader = $sql.ExecuteReader()

#write database names to array
$AllDBs = [System.Collections.ArrayList]@()
while ($myreader.Read()) {
   $arrayPos = $AllDBs.Add($myreader.GetString(0))
}
$myreader.Close()

$AllDBs = $AllDBs | sort

$outputText = ""
$isFirstDB = $true

foreach ($CurrentDB in $AllDBs) {
   $replacedQuery = $query.Replace("{db}", "``" + $CurrentDB + "``");
   $sql.CommandText = $replacedQuery
   $headerstring = ""
   $rowString = ""
   $rowcount = 0
   
   try {
      $myreader = $sql.ExecuteReader()
      while ($myreader.Read()) {
         #loop over columns
         for ($i = 0; $i -lt $myreader.FieldCount; $i++) {
            if ($isFirstDB -eq $true) {
               $headerstring += $myreader.GetName($i) + ','
            }
            $rowString += $myreader.GetString($i) + ','
         }
         if ($isFirstDB -eq $true) {
            #Add a header row of column names to the output.
            $outputText += $headerstring + "Database,`n"
            $isFirstDB = $false
         }
         $outputText += $rowString + "$CurrentDB,`n"
         $rowcount++
      }
      $myreader.Close()
      Write-Output "{$CurrentDB}: {$rowcount} rows"
   }
   catch {
      Write-Warning "{$CurrentDB} threw and error"
      $myreader.Close()
   }

   $limit--
   if ($limit -le 0) {
      break
   }
}

$myreader.Close()

$Connection.Close()

$filepath = $env:USERPROFILE + "\Desktop\Multiquery.csv"
Out-File -FilePath $filepath -InputObject $outputText -Encoding UTF8 

exit 0