find-unused-columns.php

<?php

namespace Sim;

$wwwDir = __DIR__ . '/..';
define('WWW_DIR', $wwwDir); // BC

require_once $wwwDir . '/libs/Nette/loader.php';
require_once $wwwDir . '/app/inc/Configurator.php';

$configurator = new Configurator;
$configurator->enableDebugger();
$configurator->createRobotLoader()->register();

$context = $configurator->createContainer();

$dbname = $context->parameters['database']['database'];

$res = $context->dibi->connection->query('
	SELECT [table_name], [column_name]
	FROM [information_schema.columns]
	WHERE [table_schema]=%s', $dbname
);

foreach ($res as $node)
{
	clearLine();
	echo "testing {$node->table_name}:{$node->column_name}";

	$ignore = (int) $context->dibi->connection->query('
		SELECT Count(*) as [count]
		FROM %n
	', $node->table_name)->fetchSingle('count') === 0;
	if ($ignore) {
		clearLine();
		continue;
	}

	$count = (int) $context->dibi->connection->query('
		SELECT Count(*) as [count]
		FROM %n
		WHERE %n IS NOT NULL
	', $node->table_name, $node->column_name)->fetchSingle('count');
	if ($count === 0) {
		clearLine();
		echo "{$node->table_name}:{$node->column_name}\n";
	}
}
echo "\n";

function clearLine()
{
	echo "\r" . str_repeat(' ', 140) . "\r";
}