Even after working with a database schema for over a year now, I still have to think hard about (and sometimes just guess) some column names (e.g. record.idInteractionRecent vs record.idRecentInteraction, and moduleInfo.valueShort vs moduleInfo.nameShort). The below PHP script will generate a list of column names that can be used in VIM for autocompletion.

<?php

    //  This script outputs a complete list of fully qualified database
    //  columns (ie. table.column) for the specified MySQL database suitable for
    //  VIM's autocompletion feature.
    //
    //  To add to Vim's complete list:
    //  set complete+=k/path/to/dictionary isk+=.,
    //
    //  - Matthew  ([email protected])


    $usage = <<<EOT

Usage:
php {$_SERVER['argv'][0]} username:[email protected]/database
php {$_SERVER['argv'][0]} [email protected]/database
php {$_SERVER['argv'][0]} host/database


EOT;


    if(count($_SERVER['argv']) != 2)
    {
        die($usage);
    }

    if(preg_match("#^(.*):(.*)@(.*)/(.*)$#i", $_SERVER['argv'][1], $matches) == 1)
    {
        list(,$username, $password, $host, $database) = $matches;

        if(($conn = @mysql_connect($host, $username, $password)) === false)
        {
            die("Unable to connect to {$_SERVER['argv'][1]}\n" . mysql_error($conn) . "\n");
        }
    }
    else if(preg_match("#^(.*)@(.*)/(.*)$#i", $_SERVER['argv'][1], $matches) == 1)
    {
        list(, $username, $host, $database) = $matches;

        if(($conn = @mysql_connect($host, $username)) === false)
        {
            die("Unable to connect to {$_SERVER['argv'][1]}\n" . mysql_error($conn) . "\n");
        }
    }
    else if(preg_match("#^(.*)/(.*)$#i", $_SERVER['argv'][1], $matches) == 1)
    {
        list(, $host, $database) = $matches;

        if(($conn = @mysql_connect($host)) === false)
        {
            die("Unable to connect to {$_SERVER['argv'][1]}\n" . mysql_error($conn) . "\n");
        }
    }
    else
    {
        die($usage);
    }


    if(@mysql_select_db($database, $conn) === false)
    {
        die("Unable to select database '$database'\n" . mysql_error($conn) . "\n");
    }


    $tableList = queryGetFirstColumn($conn, 'SHOW TABLES');

    foreach($tableList as $tableName)
    {
        $columnList = queryGetFirstColumn($conn, "DESCRIBE `$tableName`");

        foreach($columnList as $columnName)
        {
            printf("%s.%s\n", $tableName, $columnName);
        }
    }


    function queryGetFirstColumn($conn, $sql)
    {
        if(($query = mysql_query($sql, $conn)) == false)
        {
            die("Unable to execute query '$sql'\n" . mysql_error($conn) . "\n");
        }

        $rows = array();

        while(($row = mysql_fetch_row($query)) !== false)
        {
            $rows[] = $row[0];
        }

        return $rows;
    }