T5 Table size dashboard widget Print a list of table sizes into your dashboard.

t5-table-size-dashboard-widget.php

<?php
/**
 * Plugin Name: T5 Table size dashboard widget
 * Description: Print a list of table sizes into your dashboard.
 * Plugin URI:
 * Version:     2013.01.07
 * Author:      Thomas Scholz
 * Author URI:  http://toscho.de
 * Licence:     MIT
 * License URI: http://opensource.org/licenses/MIT
 */

add_action(
	'wp_loaded',
	array ( 'T5_Table_Size_Dashboard_Widget', 'get_instance' )
);

class T5_Table_Size_Dashboard_Widget
{
	/**
	 * Plugin instance.
	 *
	 * @see get_instance()
	 * @type object
	 */
	protected static $instance = NULL;

	protected $capability;

	protected $handle = 't5_tsdw';

	/**
	 * Access this plugin’s working instance
	 *
	 * @wp-hook wp_loaded
	 * @return  object of this class
	 */
	public static function get_instance() {

		NULL === self::$instance and self::$instance = new self;

		return self::$instance;
	}

	/**
	 * Constructor. Registers the actions.
	 *
	 * @wp-hook wp_loaded
	 */
	public function __construct()
	{
		$this->capability = apply_filters( $this->handle . '_cap', 'manage_options' );

		if ( ! current_user_can( $this->capability ) )
			return;

		add_action(
			'wp_dashboard_setup',
			array ( $this, 'register_dashboard_widget' )
		);
	}

	/**
	 * Register the dashboard widget.
	 *
	 * @wp-hook wp_dashboard_setup
	 * @return void
	 */
	public function register_dashboard_widget()
	{
		wp_add_dashboard_widget(
			$this->handle . '_widget',
			'Table sizes',
			array ( $this, 'render_dashboard_widget' )
		);
	}

	/**
	 * Show widget in dashboard.
	 *
	 * @return void
	 */
	public function render_dashboard_widget()
	{
		$this->get_current_site_stats();
		$this->get_all_tables_stats();
	}

	/**
	 * Data about this site's tables.
	 *
	 * @return void
	 */
	protected function get_current_site_stats()
	{
		global $wpdb;

		$query = "SELECT
		table_name,
		table_rows,
		concat(round(data_length/(1024*1024),2), 'M') data,
		concat(round(index_length/(1024*1024),2), 'M') idx,
		concat(round((data_length + index_length)/1024,2), 'M') total_size
		FROM information_schema.TABLES
		WHERE information_schema.TABLES.table_schema='" . DB_NAME . "'
		ORDER BY (data_length+index_length) DESC";

		if ( ! $results = $wpdb->get_results( $query, ARRAY_A ) )
			return $wpdb->print_error();

		print '<h2>Current site (<code>' . DB_NAME . '</code>)</h2>
		<table class="widefat">
		<thead>
		<tr>
		<th>Name</th>
		<th>Rows</th>
		<th>Data</th>
		<th>Index</th>
		<th>Total Size</th>
		</tr>
		</thead>
		<tbody>';

		$i = 0;
		foreach ( $results as $result )
		{
			printf(
				'<tr%1$s>
				<td>%2$s</td>
				<td>%3$s</td>
				<td>%4$s</td>
				<td>%5$s</td>
				<td>%6$s</td>
				</tr>',
				0 === $i++ % 2 ? ' class="alternate"' : '',
				$result['table_name'],
				$result['table_rows'],
				$result['data'],
				$result['total_size'],
				$result['idx']
			);
		}

		print '</tbody></table>';
	}

	/**
	 * Data about all tables in current database.
	 *
	 * @return void
	 */
	protected function get_all_tables_stats()
	{
		global $wpdb;

		$query = "SELECT
		count(*) tables,table_schema,
		concat(round(sum(table_rows)/1000000,2),'M') rows,
		concat(round(sum(data_length)/(1024*1024),2),'M') data,
		concat(round(sum(index_length)/(1024*1024),2),'M') idx,
		concat(round(sum(data_length+index_length)/(1024*1024),2),'M') total_size
		FROM information_schema.TABLES
		GROUP BY table_schema
		ORDER BY sum(data_length+index_length) DESC;";

		if ( ! $results = $wpdb->get_results( $query, ARRAY_A ) )
			return $wpdb->print_error();

		print '<h2>All tables</h2>
		<table class="widefat">
		<thead>
		<tr>
		<th>Tables</th>
		<th>Schema</th>
		<th>Rows</th>
		<th>Data</th>
		<th>Index</th>
		<th>Total Size</th>
		</tr>
		</thead>
		<tbody>';

		$i = 0;
		foreach ( $results as $result )
		{
			printf(
				'<tr%1$s>
				<td>%2$s</td>
				<td>%3$s</td>
				<td>%4$s</td>
				<td>%5$s</td>
				<td>%6$s</td>
				<td>%7$s</td>
				</tr>',
				0 === $i++ % 2 ? ' class="alternate"' : '',
				$result['tables'],
				$result['table_schema'],
				$result['rows'],
				$result['data'],
				$result['idx'],
				$result['total_size']
			);
		}

		print '</tbody></table>';
	}
}