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.
Just wanted to thank you for sharing. From your post a few years back, which I just read today, I tried
CapsLock::
Send {End}
return
and it works! Maybe it is a no-brainer for most but since I am a beginner I appreciate a lot that you shared your file.
Do you have any suggestions for having a dynamic AHK GUI for writing SQL select queries? A Gui where the user can simply select the functions, clauses and/or operators, fill in the table and column names and then the complete query is built by the application. That can help anyone who is aware of the basics, but is not hands-on the way a query should be built.