More AutoHotKey goodness (database edition)

I posted a few years back about some of the macros that I have in my AutoHotKey file. Since then I’ve added some new things that are worth mentioning.

Inner joins

The project that I’m on involves a lot of database work. I’m writing queries all the time, and this means typing the same inner joins over and over. I would rather let AutoHotKey do that for me.

Here’s a query that I write often:

select * from igs.accounts a
inner join igs.enrollments e on e.accountid = a.accountid
inner join igs.intentions i on i.enrollmentid = e.enrollmentid
where a.utilitylineofbusinessid = 5

It’s incredibly painful to type those inner joins over and over (it was even painful writing it for this blog post), so I use AutoHotKey to do it.

:*:ea]::inner join igs.enrollments e on e.accountid = a.accountid
:*:ae]::inner join igs.accounts a on a.accountid = e.accountid

:*:ie]::inner join igs.intentions i on i.enrollmentid = e.enrollmentid
:*:ei]::inner join igs.enrollments e on e.enrollmentid = i.enrollmentid

:*:wcpa]::where utilitylineofbusinessid = 5

:*:ssf]::
SendEvent select * from `
return

So now to type that same query, here is what I type:

ssf]igs.accounts a
ea]
ie]
wcpa]

I have macros for virtually every inner join I would ever do in the database so that I don’t ever have to type one out. If I find one that I missed, I go back and add it.

This makes writing queries so much easier. Once you find ways to do mundane things faster, it makes you do everything faster because your typing is keeping up with your brain.

Switching databases

We have several database environments just like most of you, which means that I’m switching between database servers all the time in SQL Server Management Studio. Again, I can pick up the mouse and click 5 times in dialogs and toolbars to switch. Or I can do “Alt-q ch Ctrl-u i”. But I would rather do it with one keystroke.

#^1::
Send !qch
Send thedatabaseserver
Sleep 200
Send {Enter}
Sleep 100
Send ^ui{Enter}
return

Boom! Ctrl-Win-1 and I’ve switched to my database server and selected my database (the “Send ^ui{Enter}” line does that – Ctrl-U selects the database dropdown and “i” selects my database because my database name starts with “i”). I have shortcuts for all of my database environments so that I can do even less with the mouse than ever.

Commonly used queries

There are certain queries that I use a lot (e.g. select the top 1000 rows from our log table). So I wrote a macro for that.

:*:seq]::select top 1000 DateCreated d, * from igs.ServiceExceptions order by DateCreated desc

Typing is slow

Typing is slow (and I’m a faster typist than most), so anything repetitive that I do often I want to turn into a macro or keyboard shortcut. Hopefully some of these ideas will give you ideas of how you can improve your productivity.