Using Emacs As Your SQL Interface

As part of learning a single editor well (Pragmatic Programmer Tips), I’ve switched to using Emacs as my interface to SQL Server.

Most of the following tips work equally well for using Emacs with other SQL databases besides MS SQL Server.

How to connect to SQL Server using Emacs

First you need to connect to a database. The command for connecting to a MS SQL Server instance is “sql-ms”.
Once you’ve entered the connection information Emacs opens a SQL query buffer, in which you can type SQL commands. After you type a SQL command, don’t forgot to enter “go” to execute the command.

Helpful commands for retrieving schema information

1. select * from sys.tables
Display the list of database tables

2. select left(column_name, 35), left(data_type, 15), is_nullable from information_schema.columns where table_name=’tablename’
Display the column names and column data-types for the table, ‘tablename’.

3. select left(CONSTRAINT_NAME, 100), CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name=’tablename’
Display the constraints for table, ‘tablename’.

4. select left(@@servername, 25), left(DB_NAME(), 25)
Display the database server name and the database name.

Improving Data Display

To improve line wrapping execute “M-x toggle-truncate-lines”, this prevents the soft line wrapping that Emacs normally does and improves the display of long lines.

To make the display of wide columns (such as varchar or char(8000)) readable use select left(columnName, 25) from table, this truncates the column width to 25 characters.

Useful Shortcuts

Because sql-ms and the other sql providers inherit from comint you can use all the nice emacs command line shortcuts. I use “C-c-l” to list the command history, “M-p” and “M-n” to cycle through commands, and “sql-send-buffer” (C-c-C-b) to send a buffer to the *SQL* mode buffer, this is extremely useful if I’m trying to tweak a large query. Use sql-set-sqli-buffer to set the SQL interactive buffer that’s used by sql-send-buffer.

Advertisements