Skip to content

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

February 11, 2020
T-SQL Tuesday logo

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.

From → Uncategorized

3 Comments
  1. Ryk McDorman permalink

    Wish I’d have known about this years ago — thanks!! In my version of SSMS it’s under Tools>Options>Environment>Keyboard>Query Shortcuts

    • Ryk McDorman permalink

      I also needed to close and reopen SSMS before the shortcut would take effect.

Trackbacks & Pingbacks

  1. T-SQL Tuesday #123: Summary of Life Hacks – Jess Pomfret

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: