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