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 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:


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"

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"


Image: Example of PowerShell Input

Image: Example of PowerShell Output

Image: Example of Text File Output

No comments:

Post a comment