Sunday, August 26, 2012

Keyword tab completion in sqsh(MsSQL console client in Linux)

I am one of those unlucky guys who have to maintain software that uses Microsoft SQL server. So i need to manipulate data on MsSQL server from time to time. Squirrel SQL GUI client can help me to do this. It's a great tool that can work with any JDBC compliant databases, but using GUI tool it's not always an option. I've done some googling and found a blog post that explains how to connect to Microsoft SQL server from linux via console using sqsh.Here, i am going to outline a few steps for configuring sqsh:

Install sqsh and freetds from software repository or build it from source.NOTE: if building from source don't forget to enable readline support.

Configure database servers in freetds.conf file. freetds config file is located in /usr/local/etc/ or /etc/freetds folder.

Now you can manipulate data via console. It works well, but it's missing autocomplete of table and column names. Luckily sqsh has -k(keywords) parameter that let's you specify a file containing a list of keywords to be used for keyword tab completion(see manual). So we need to create a list of all table and column names presented in our database, store it in ~/.sqshrc_keywords data file and feed it to the sqsh on startup.Next sql statement will create dictionary that will be used by tab completion.