Emacs can be used to talk to MS SQL-Server. The benefits include, among other things, that you get a history of all of your commands in a single text file and you can bring up previously entered comands with M-n and M-p just like you do at the Lisp REPL prompt. Sometimes its just not worth opening up Enterprise Manager if you only have a quick thing or two to edit or check on. And if you’re doing your development in EMACS, then you might as well stay inside of it instead of jumping out to another program….
To connect, just type M-x sql-ms. You’ll need to know your server name and password and stuff, of course, when the command window prompts you. Once in, you can type SQL commands to your heart’s content. If you’re wondering why your commands don’t return anything you either forgot to type “GO” to send the command… or maybe there was a syntax error in your SQL. Type something like “Select 1” and “GO” to find out what the error message was. (For some reason, you have to send a working query in order to get the error message back.)
The following queries can replace some common tasks done from within Enterprise Manager:
–Get Table List
SELECT SUBSTRING(name, 1, 20) FROM sysobjects WHERE xtype = ‘U’ ORDER BY 1
–Get Columns List
SELECT SUBSTRING(name, 1, 20) FROM syscolumns WHERE id=object_id(‘Your_Table_Name_Here’)
–Get Stored Procedure List
SELECT SUBSTRING(name, 1, 20) FROM dbo.sysobjects WHERE (type = ‘P’) ORDER BY name
–Get Stored Procedure Parameters
SELECT SUBSTRING(parameter_mode, 1, 10), SUBSTRING(parameter_name, 1, 20), SUBSTRING(data_type,1,10)
FROM information_schema.parameters
WHERE specific_name=’Your_Proc_Name_Here’
–Get Text of Stored Procedure (ugly)
sp_helptext ‘Your_Proc_Name_Here’
And now the bad news…. Due perhaps to quirks of both isql and emacs, queries with several columns can look pretty ugly when you get their result set. That’s why I used SUBSTRING in the commands above. (I can’t figure out how to turn off the automating line-wrapping.) For one thing, the column names get shifted over annoyingly because a string representing the amount of time it took to run the query gets printed at the beginning of that line. Also the column widths are generally way too big.
If you want to take the text of a stored procedure to another buffer, just type C-SPACE to set the mark after you type GO for that command and just before you hit ENTER to execute the SQL. When the results are returned type M-w to put it in the kill ring. Use C-x C-f to open up a new file… and C-y to paste in your stored procedure code. If you’re going to be editing a lot of stored procedures then you’ll probably want a macro to clean up all of the erroneous white space you’re gonna be pulling in with this process. Type C-x (to start recording a macro. Type C-M-% to do a regex replace. Replace [ ~]+~[ ~]+ with ~, where the ~ character is a carriage returned entered by typing C-q C-j. Type M-< to go to the top of the file. (Each square bracket should contain both a space and a carriage return.) Type ! to apply the replace to the whole file from the cursor point down. Then type C-x ) to finish recording the macro. To reuse the macro on other files, just type C-x e.
This is overall a pretty mediocre experience, but it demonstrates that it is possible to work on SQL Server from Emacs. With the right .emacs file, though, much of this can be streamlined and made easier to deal with…. Of course, you may not always be working on a machine that has Enterprise Manager installed, in which case going this route allows you to avoid some of the irritating free-ish versions of Query Analyzer that I’ve seen floating around….
Update 7/12/2007:
Fix the Word-Wrap nightmare while in the sql-mode with M-x toggle-truncate-lines.
Learning Lisp 