How to auto-format and auto-capitalize SQL in Vim

featuressqlvim

I'd like to use vim to write SQL queries, but haven't figured out how to get two key features that I use all the time in programs like SQL Yog.

I usually start an instance of Yog in the background, cut-and-paste my SQL into it, hit F12 which auto-formats the whole thing. All the SQL keywords are automatically capitalized as soon as the text is pasted. I then copy it back into whatever other tool I'm using (often SQL Query Browser at work) and go from there. The benefits of these two features are worth the extra effort.

Does anyone know how I can set up vim to do this? I still may need to cut and paste back into Query Browser, which is acceptable to me, but I'd like to stick with vim for all my editing.

Best Answer

You could put some autocmds for SQL files in your .vimrc that execute a bunch of :ab commands to auto-replace common keywords with their capitalized variants.

Going further, you could also write your own indentation script and attach it to indentexpr.

If you need to do this inline in another file type (i.e., you're writing SQL queries in strings in, say, PHP), you'll probably have to do this in a scratch buffer anyway, or modify your language's ftplugin to use your indentexpr in an "SQL mode."

Related Solutions

SQL Performance – Internationalization Through SQL Database and Performance Issues

Joins are good, use joins. For them to be effective though the join fields must be indexed as well as fields you intend to use in where clauses. Foreign Keys are not indexed automatically in SQL Server and I imagine they aren't in most other dbs either. PKs are generally indexed bu only if you formally make them a PK (which of course you should do).

Thousands of records is teeny tiny for a database. Our medium sized db has around 20 million records in one of it's main tables and we don't have performance problems. And we often join to 15-20 tables.

Node.js – Preserving Pre-formatted Multi-Line Strings in Scripts

JavaScript now supports multi-line strings via the backtick sytax. For example:

const foo = `$query = <<<EOT
select
     field1
    ,field2
    ,field3
from tableName
where
    field1 = 123
EOT;`

References

Best Answer

Related Solutions

SQL Performance – Internationalization Through SQL Database and Performance Issues

Node.js – Preserving Pre-formatted Multi-Line Strings in Scripts

Related Topic