Saturday, 18 April 2020

How to Describe All Tables in All Databases from a MySQL Server

I was curious to see if I could get an output from a lab MySQL server, listing all databases on the MySQL server, all tables in these databases, and all fields in these tables.

First get the ‘MySQL PowerShell cmdlets module’ from https://gallery.technet.microsoft.com and install. The download msi is called ‘it.wiechecki.mysql-cmdlet.msi’.

Then, copy and paste the text below into a text editor and save as say ‘DescribeAllTablesInAllDatabasesMySQL.ps1’ (doesn’t really matter what you call it.) Since this is just a curiosity, I didn’t bother with any error detection or anything like that. And run in PowerShell:

.\DescribeAllTablesInAllDatabasesMySQL.ps1

The Script


Import-Module MySqlCmdlets

## INPUT ##

$S = Read-Host "Server"
$U = Read-Host "Username"
$P = Read-Host "Password" -AsSecureString
Connect-MySQLserver -Server $S -UserName $U -Password $P
[System.Object]$DBs = Invoke-MySqlQuery -Query "show databases"
[System.Object]$Tables = @{}
[System.Object]$Describes = @{}
$DBs | Foreach{
  [String]$DBname = $_.Database
  [System.Object]$Describes.$DBname = @{}
  Invoke-MySqlQuery -Query "use $DBname"
  [System.Object]$Tables.$DBname = Invoke-MySqlQuery -Query "show tables"
  Foreach($Table in $Tables.$DBname."Tables_in_$DBname"){
    Write-Host ($DBname + "." + $Table)
    [System.Object]$Describes.$DBname.$Table = Invoke-MySqlQuery -Query "describe $Table"
  }
}

## OUTPUT ##

$CountD = 0
$CountT = 0
$CountF = 0
[System.Array]$Output = @()

$DBs | Foreach{
  [String]$DBname = $_.Database
  Foreach($Table in $Tables.$DBname."Tables_in_$DBname"){
    Foreach($Field in $Describes.$DBname.$Table.field){
      Write-Host $DBname -F GREEN -N
      Write-Host ("." + $Table) -F CYAN -N
      Write-Host ("." + $Field) -F YELLOW
      $Output += "$DBname.$Table.$Field"
      $CountF++
    }
    $CountT++
  }
  $CountD++
}

Write-Host "$CountF fields " -F YELLOW -N;Write-Host "across " -N
Write-Host "$CountT tables " -F CYAN -N;Write-Host "across " -N
Write-Host "$CountD databases " -F GREEN -N
$Output += "$CountF fields across $CountT tables across $CountD databases"
$Output > "All_Tables_in_all_Databases_described.txt"


Images

Image: Example of PowerShell Input

Image: Example of PowerShell Output

Image: Example of Text File Output

No comments:

Post a comment