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:
Next sql statement will create dictionary that will be used by tab completion.
Use this script to connect to MsSQL database.
Now it's possible to autocomplete table and column names with tab:
References:
[1] Squirrel SQL client
[2] How to connect to Microsoft SQL server from Linux via command line
[3] SqSh
[4] FreeTDS
[5] Finding and listing all columns in a SQL Server database.
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.
[server1] host = myserver port = 1433 tds version = 8.0 NOTE: You can find out path to the freetds.conf file by executing tsql -C | grep freetds.conf command.
- Configure default connection settings and style. The output of ~/.sqshrc should look like this:
\set username=sa \set password=password \set database=DB_NAME \set style=pretty \set colsep="|" \set semicolon_hack=1 \set keyword_completion=3
- Connect to MsSQL server by specifying server name from freetds.conf and file with connection settings:
sqsh -S server1 -r ~/.sqshrc NOTE: By default sqsh will always use ~/.sqshrc file.
Next sql statement will create dictionary that will be used by tab completion.
In this blog post you can find more information on how to get listing of all table and column names in MsSQL. Store this sql statement in ~/.sqshrc_keywords_sql file.SELECT SO.NAME FROM dbo.sysobjects SO WHERE SO.xtype = 'U' ORDER BY SO.[name]; go SELECT SO.NAME + '.' + SC.NAME FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id WHERE SO.xtype = 'U' ORDER BY SO.[name], SC.colid; go
Then create a script that will generate SQL dictionary and establish a connection with the database:
sqsh -S server1 -m bcp -i ~/.sqshrc_keywords_sql -o ~/.sqshrc_keywords;
sed -i.bak 's/|//' ~/.sqshrc_keywords;
sqsh -S server1 -k ~/.sqshrc_keywordsUse this script to connect to MsSQL database.
Now it's possible to autocomplete table and column names with tab:
References:
[1] Squirrel SQL client
[2] How to connect to Microsoft SQL server from Linux via command line
[3] SqSh
[4] FreeTDS
[5] Finding and listing all columns in a SQL Server database.