Drop orphaned tables from WordPress multisite with multiple networks.

drop-orphaned-tables.php

<?php
add_action('init', function (){
	global $wpdb;
	$db_schema = 'zuse';
	$output = '';
	$networks = get_networks();
	$sites = array();
	$site_ids = array();
	$in_wp = array();
	$not_in_wp = array();

	/* Collect site IDs from WordPress perspective */
	foreach ($networks as $network){
		switch_to_network($network->id);
		$sites = get_sites();
		foreach ($sites as $site){
			$site_ids[] = $site->blog_id;
		}
		restore_current_network();
	}

	$db_tables = $wpdb->get_results( "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE '$db_schema'",OBJECT );

	$output .= count($db_tables).' Tables<br>';

	/* Collect site IDs from database perspective, and compare with WordPress perspective */
	foreach ($db_tables as $db_table){
		preg_match_all('!\d+!', $db_table->TABLE_NAME, $db_site_id);

		if(!is_numeric($db_site_id[0][0])){
			// Not tied to site number ex. wp_options
			$in_wp[] = $db_table->TABLE_NAME;
			continue;
		}

		if(!in_array($db_site_id[0][0], $site_ids)){
			// Site ID does not exist in WordPress. DB table should be DELETED
			$not_in_wp[] = $db_table->TABLE_NAME;
			continue;
		}

		if(in_array($db_site_id[0][0], $site_ids)){
			// Matches an existing WordPress site. DO NOT delete!
			$in_wp[] = $db_table->TABLE_NAME;
			continue;
		}
	}

	/** DROP TABLES for sites that do not exist in WordPress */
	$drop_statement = 'DROP TABLE IF EXISTS `'.implode('`,`',$not_in_wp).'`';
	$wpdb->query( $drop_statement);
	
	/* Output findings */
	$output .= '<h1>In WordPress</h1>';
	$output .= count($in_wp);
	$output .= '<h1>NOT In WordPress</h1>';
	$output .= count($not_in_wp);
	echo $output;
});