Home

Awesome

mssql

Command line tool for connecting to Microsoft Sql Server from Mac or Linux.

Installation

gem install mssql

It depends on freetds. So it should be installed.

On Mac:

brew install freetds

On Ubuntu Linux:

sudo apt-get install freetds-dev

Usage

Usage: mssql <options>

-c, --connection CONNECTION      use connection defined in ~/.mssql
-h, --host HOST                  server host
-u, --username USERNAME          username
-p, --password PASSWORD          password
-d, --database DATABASE          use database name
-i, --input_file INPUT_FILE      input file name
-q, --query QUERY                run query and exit
-?, --help                       show syntax summary

Connect to database:

$ mssql -h host -u user -p password -d database

Use connection from config file (~/.mssql):

$ mssql -c alfa

Execute query and exit:

$ mssql -c alfa -q "select * from authors"

Commands

Commands are prefixed with dot. Currently recognized commands are:

query

pubs> use pubs
pubs> select top 5 * from authors
pubs> go

+-------------+----------+----------+--------------+----------------------+------------+-------+-------+----------+
| au_id       | au_lname | au_fname | phone        | address              | city       | state | zip   | contract |
+-------------+----------+----------+--------------+----------------------+------------+-------+-------+----------+
| 172-32-1176 | White    | Johnson  | 408 496-7223 | 10932 Bigge Rd.      | Menlo Park | CA    | 94025 | true     |
| 213-46-8915 | Green    | Marjorie | 415 986-7020 | 309 63rd St. #411    | Oakland    | CA    | 94618 | true     |
| 238-95-7766 | Carson   | Cheryl   | 415 548-7723 | 589 Darwin Ln.       | Berkeley   | CA    | 94705 | true     |
| 267-41-2394 | O'Leary  | Michael  | 408 286-2428 | 22 Cleveland Av. #14 | San Jose   | CA    | 95128 | true     |
| 274-80-9391 | Straight | Dean     | 415 834-2919 | 5420 College Av.     | Oakland    | CA    | 94609 | true     |
+-------------+----------+----------+--------------+----------------------+------------+-------+-------+----------+
5 rows affected

.find

.find will list all database objects:

pubs> .find
+-----------+--------+--------------------------+
| type      | schema | name                     |
+-----------+--------+--------------------------+
| table     | dbo    | authors                  |
| table     | dbo    | discounts                |
| table     | dbo    | employee                 |
| table     | dbo    | jobs                     |
| table     | dbo    | pub_info                 |
| table     | dbo    | publishers               |
| table     | dbo    | roysched                 |
| table     | dbo    | sales                    |
| table     | dbo    | stores                   |
| table     | dbo    | sysdiagrams              |
| table     | dbo    | titleauthor              |
| table     | dbo    | titles                   |
| view      | dbo    | titleview                |
| procedure | dbo    | byroyalty                |
| procedure | dbo    | reptq1                   |
| procedure | dbo    | reptq2                   |
| procedure | dbo    | reptq3                   |
| procedure | dbo    | sp_alterdiagram          |
| procedure | dbo    | sp_creatediagram         |
| procedure | dbo    | sp_dropdiagram           |
| procedure | dbo    | sp_helpdiagramdefinition |
| procedure | dbo    | sp_helpdiagrams          |
| procedure | dbo    | sp_renamediagram         |
| procedure | dbo    | sp_upgraddiagrams        |
| function  | dbo    | fn_diagramobjects        |
+-----------+--------+--------------------------+
25 rows affected

or objects by type (tables/views/procedures/functions):

pubs> .find tables
+-------+--------+-------------+
| type  | schema | name        |
+-------+--------+-------------+
| table | dbo    | authors     |
| table | dbo    | discounts   |
| table | dbo    | employee    |
| table | dbo    | jobs        |
| table | dbo    | pub_info    |
| table | dbo    | publishers  |
| table | dbo    | roysched    |
| table | dbo    | sales       |
| table | dbo    | stores      |
| table | dbo    | sysdiagrams |
| table | dbo    | titleauthor |
| table | dbo    | titles      |
+-------+--------+-------------+
12 rows affected

.explain

.explain for procedures/functions/views returns sql body, for tables executes sp_help [table name]

iow> .explain reptq1
CREATE PROCEDURE reptq1 AS
select 
	case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id, 
	avg(price) as avg_price
from titles
where price is NOT NULL
group by pub_id with rollup
order by pub_id

.exit

Use it to close mssql.

Configuration file ~/.mssql

Mssql tries to read ~/.mssql config file on start up. Config file is in yaml format.

Example config file:

alfa: &alfa
  name: alfa
  host: alfa_host
  username: my_username
  password: my_password
  database: pubs
beta: 
  name: beta
  host: beta_host
  username: domain\domain_user
  password: password
  database: Northwind
  
default_connection:
   <<: *alfa

With config file you can start mssql using -c argument to specify connection:

mssql -c alfa

If default connection exists it will used if no arguments specified:

mssql
alfa> _

Emacs usage

I build this for use with Emacs sql-mode. Add ./emacs/sql_ms.el to your init.el:

(add-to-list 'load-path "~/Work/mssql/emacs/")
(require 'sql-ms)

Create ~/.mssql file with connections you want to use. In Emacs press F12 or M-x enter-db-mode to open two buffers: *queries* and *SQL*. Write your queries in queries buffer and watch results in SQL buffer.

Keybindings:

Thanks