T-SQL Tuesday #123: Life hacks to make your day easier – Custom shortcuts in SSMS/ADS

This is my first time taking part in T-SQL Tuesday. It’s something I’ve known about for a while but not taken part in before. But one of my goals this year is to post at least once a week on this blog for 3 months, so I figure I should take every prompt I can. Plus this week’s topic is something I feel I can contribute to.

This week’s topic is about any hacks you have to make your day to day life easier, and I wanted to share a little trick someone showed me about 10 years ago, that I’ve found incredibly useful ever since.

The basics of it is, you can add your own query shortcuts to SSMS, and when you run them they append any highlighted text to the shortcut code. To explain, I’ll use the example I’ve found most useful:

One of the first things I do whenever I start a new job or install a new version of SSMS is I set up ctrl+5 as the shortcut for “SELECT TOP 100 * FROM “

Once I’ve done that I can highlight any table or view in my code, and use ctrl+5 to see 100 rows from that object, because it appends the highlighted text to the end of my shortcut text. But you can do more with it. Take this bit of code:

SELECT
      A.Col1
    , B.Col2
    , C.Col3
FROM TableA AS A
INNER JOIN TableB AS B
    ON A.ID = B.ID
LEFT JOIN
    (
        SELECT
              X.Col5
            , Y.Col3
            , X.ID
        FROM TableX AS X
        LEFT JOIN TableY AS Y
            ON X.ID = Y.ID
            AND X.Col3 = 'Some value'
    ) AS C
    ON A.ID = C.ID

I can highlight TableA, TableB, TableX, or TableY, and in each case Cltrl+5 will show me all of the columns in the table and a sample of the data. Or I can highlight TableA AS A INNER JOIN TableB AS B ON A.ID = B.ID and get a sample of all the columns available from that. Or I can do something similar inside the subquery and see all the columns I have available to me in that subquery.

The main other shortcut I like to set is Ctrl+6 as “SELECT SUM(1) FROM “, to tell me how many rows are in a table.

If you want to set these up it’s very easy:

In SSMS go to Tools>Options>Keyboard>Query Shortcuts.

In Azure Data Studio it’s a little more hidden. You might think you’d be able to do this in File>Preferences>Keyboard Shortcuts, but that only gives you a list of pre-canned commands that you can assign to different shortcuts as you see fit. To write your own you need to go to Cog icon(bottom left)>Settings>Data>Query to create your own shortcuts. You need to be a bit careful because there will already be other shortcuts assigned to your key combinations, so you will need to go into File>Preferences>Keyboard Shortcuts to move them to other shortcuts, otherwise Data Studio will try and do both things when you use your shortcut.

Anyway, that’s it, hopefully someone will find this useful.