hwechtla-tl: Handy command line client for tds databases

Mikä on WikiWiki?
nettipäiväkirja
koko wiki (etsi)
viime muutokset

---

(nettipäiväkirja 07.01.2015) Somehow I wasn't able to find a proper SQL monitor or command line for Sybase / MS SQL server. sqsh is kinda close, but I couldn't find a good way to get nice output out of sqsh (by default). The problem is in particular the length of TDS output; if every table row is many lines long, it gets very hard to read the output. So I wrote this on bsqldb from FreeTDS.

It's not the safest way to do this, ps might show someone the command line of the running bsqldb which contains the password. I just wanted to share this for ease of use if you're pulling your hair with isql/sqsh/tsql/whatever.

#!/bin/bash

test "X$1" = X && rlwrap $0 --interactive && exit 0

SERVER=bar.example.com
DATABASE=foo
USER=z
read -p "Give password for $USER: " -s PASSWORD && echo

exec_sql() {
        echo "$*" | \
        bsqldb -h -t '|-|' -S "$SERVER" -D "$DATABASE" -U "$USER" -P "$PASSWORD"
}

to_html() {
        echo '<table>'
        sed 's#&#\&amp;#g;s#<#\&lt;#g;s#>#\&gt;#g' | \
        sed 's#^#<tr><td>#;s#$#</td></tr>#;s#|-|#</td><td>#g' | \
        sed '/<tr>\(<td>-*<\/td>\)*<\/tr>/{;s#.*#</table><table>#p;d;};${;x;p;};x'
        echo '</table>'
}

while true; do
        read -p "sql> " QUERY
        exec_sql "$QUERY" | to_html | w3m -dump -T text/html
done

If you just want CSV instead of tabulated output, replace | to_html | w3m -dump -T text/html with e.g. | sed 's/|-|/;/g' | less (or something similar, or leave it out altogether).

Some starting points:

• list all tables in your database:

sql> sp_tables

• list all kinds of stuff in your database:

sql> select name,type from sysobjects

• show the definition of a table (this information comes from syscolumns):

sql> sp_help tablename

• show the definition of a view, stored procedure or similar (this information comes from syscomments):

sql> sp_helptext viewname

• merkintä: 2015-01
• atehwa
• kategoria: päiväkirjamerkintä
• kategoria: työkalut