fixTableStructures.php

<?php
/**
 * This script will attempt to sycronize table structures in a multisite wordpress install
 *
 * Crete a backup before running this script
 */
$host = $dbname = $username = $password = '';
require 'connection_settings.php';
$prefix = substr(basename(__FILE__), 0, -4);

$script = fopen($prefix . '.sql', 'w');
$dbh = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

$debugging = false;
$checked = $updated = 0;
foreach($dbh->query('SHOW TABLES') as $row) {
	$table = $row[0];

	if (!preg_match('@^wp_(\d+)_@', $table, $match)) {
		continue;
	}
	$checked++;

	$mainTable = preg_replace('@wp_(\d+)_@', 'wp_', $table);

	$return = $dbh->query("SHOW CREATE TABLE $mainTable");
	if (!$return) {
		if ($debugging) {
			echo "Checking $table, Table $mainTable doesn't exist, skipping\n";
		}
		continue;
	}
	$result = $return->fetch();
	$createTable = $result[1];
	if (!$createTable) {
		if ($debugging) {
			echo "Checking $table, invalid create table statement returned for $mainTable, skipping\n";
		}
		continue;
	}
	$createTable = preg_replace('@AUTO_INCREMENT=\d+ @', '', $createTable);
	$createTable = str_replace($mainTable, $table, $createTable);


	$return = $dbh->query("SHOW CREATE TABLE $table");
	$result = $return->fetch();
	$currentTable = $result[1];
	$currentTable = preg_replace('@AUTO_INCREMENT=\d+ @', '', $currentTable);

	if ($currentTable === $createTable) {
		if ($debugging) {
			echo "Checking $table, definition identical to $mainTable, skipping\n";
		}
		continue;
	}

	echo "Regenerating $table from $mainTable definition\n";
	$updated++;

	$query = "RENAME TABLE $table TO {$prefix}_{$table}";
	fwrite($script, $query . ";\n");
	$dbh->query($query);

	fwrite($script, $createTable . ";\n");
	$dbh->query($createTable);

	$query = "INSERT INTO $table SELECT * FROM {$prefix}_{$table}";
	fwrite($script, $query . ";\n");
	$dbh->query($query);

	$query = "DROP TABLE {$prefix}_{$table}";
	fwrite($script, $query . ";\n");
	$dbh->query($query);
	fwrite($script, "\n");
}

if ($updated) {
	echo "Checked $checked tables, updated $updated\n";
} else {
	echo "Checked $checked tables, all have the correct structure\n";
}