#!/usr/bin/perl
use DBI;
my $username;
my $password;
my $hostname;
my $dbName;
my $databasez;
my $withRepair = 0; #1=enabled  ##Also checks and repairs the table if required.
$username='USERNAME';
$password='PASSWORD';
$hostname='HOSTNAME';
$dbName='';   #(Optional) considers all if left blank
$databasez='';
$dbName ='';
my $port = 3307;
my $dbh;

$dbh = DBI->connect("dbi:mysql:database=$dbName;host=$hostname;$port", trim($username), trim($password)) or die "$DBI::errstr";

if(length(trim($databasez)) == 0)
{
	my $db_SQL="SHOW DATABASES;";
	$sth=$dbh->prepare($db_SQL);
	$sth->execute();

	while($databasez = $sth->fetchrow_array())
	{
			if ((trim($databasez) eq "information_schema")  or (trim($databasez) eq "mysql")) {
				next;
			}
			tablesCount($databasez);
	}
}
else {
	tablesCount($databasez);
}

sub trim($)
{
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	return $string;
}

sub execute($)
{
	my $sth_analyze_table = $dbh->prepare(shift);
	$sth_analyze_table->execute() or $dbh->errstr;
	my $status2 = $sth_analyze_table->fetchrow_array();
	printf "%35s %10s %10s\n",$tablez,$status,$status2;
}

sub tablesCount($)
{
	my $Line = "-----------------";
	printf "\n%20s\n",$Line;
	printf "\nDatabase:%35s\n",$databasez;
	printf "\n%35s %10s %10s\n","Table Name" ,"Status", "Repair Status";
	printf "\n%20s\n",$Line;
	my $tables_SQL = "SHOW TABLES FROM $databasez;";
	my $sth_table=$dbh->prepare($tables_SQL);
	$sth_table->execute();
	while($tablez = $sth_table->fetchrow_array())
	{
		my $analyze_tables_SQL = "ANALYZE TABLE $databasez.$tablez;";
		my $optimize_table_SQL = "OPTIMIZE TABLE $databasez.$tablez;";
		my $check_tables_SQL = "repair table $databasez.$tablez;";
		
		execute($analyze_tables_SQL);
		execute($optimize_table_SQL);

		if ($withRepair == 1) 
		{
			my $check_tables_SQL = "check table $databasez.$tablez;";
			my $sth_check_table=$dbh->prepare($check_tables_SQL);
			$sth_check_table->execute() or $dbh->errstr;
			my $status = $sth_check_table->fetchrow_array();

			if(trim($status) ne 'OK')
			{
				print "Attempting Repair: $databasez.$tablez \n";
				my $check_tables_SQL = "repair table $databasez.$tablez;";
				execute($check_tables_SQL);
				my $status2 = $sth_check_table->fetchrow_array();
				printf "%35s %10s %10s\n",$tablez,$status,$status2;
			}
			else
			{
				printf "%35s %10s %10s\n",$tablez,$status,"NA";
			}
		}
	}
}