Skip to content

Instantly share code, notes, and snippets.

@howellcc
Last active October 6, 2023 19:29
Show Gist options
  • Save howellcc/1cb719880c80eb8b61d259735e3e4b99 to your computer and use it in GitHub Desktop.
Save howellcc/1cb719880c80eb8b61d259735e3e4b99 to your computer and use it in GitHub Desktop.
Script for running the same query against multiple MySQL databases on the same server and saving that result to a .csv.
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment