[Comments] SSMS Keyboard Query Shortcuts

[chadbaldwin]

https://chadbaldwin.net/2021/01/21/ssms-keyboard-query-shortcuts

[Glasses93]

Hey,

I just wanted to say this article is great! It had not crossed my mind to create parametrized query shortcuts via the sp_executesql system procedure. Even using it for a simple "SELECT TOP (100) *" is handy. I must've passed this settings window so many times and I missed all these "obvious" tricks. I've created shortcuts for getting row counts (via system views), index details, and index fragmentation of a table all at the click of a few buttons. It's almost too fun/easy!

I also found another potentially useful shortcut which would look like the following:

"IF @@SERVERNAME != N'YourDevServer' BEGIN RAISERROR(N'You''re not on the development server, by the way. This batch will abort.', 16, 1); RETURN; END; "

You don't actually pass a parameter into it - you would just use it as an F5 replacement when you want to execute unrelated queries. It would act as a precursor statement to every batch you send. Admittedly, remembering to press CTRL + 9, say, over F5 is quite hard to remember, but I thought I'd share my two cents on this, anyway. Thanks again for this article, and I look forward to reading more.

Steve

[chadbaldwin]

@Glasses93 haha! Too funny. (regarding the F5 safety) This exact usage just occurred to me last night while responding to reddit posts in r/SQLServer. Someone was talking about how they accidentally ran something in production, so they replaced their F5 key with a red one. And then this exact usage popped into my head.

I even thought about re-mapping F5 and ctrl+E to something else so that you don't hit it out of habit. Having two different execution keys, one for production and one for dev may be a good way to handle it.

[mattcargile]

Great stuff! Love the poor man's snippet deal too!

[SQL-Ronin]

Hey There! Great stuff, but I am having an issue with one and I have no idea why?

I have this assigned to Crtl+8:

SELECT FORMAT(COUNT(*),'N0')AS Row_Count FROM [space]

But when I highlight a table name, or a schema and a table name, I get....nothing. I've pasted the actual SQL into a query widow and executed it, which runs fine. So not sure.

Thanks!

[Glasses93]

Have you opened a new query since assigning the shortcut?

[SQL-Ronin]

Aaaaaahhhh! That did it!! Didn't even think of that! Thank you!

[mattcargile]

Just wrote up this script-let for my new v20 install. Does the backup and has error handling if the QueryShortcuts XmlElement doesn't exist. Thanks again Chad for putting this together.

[CmdletBinding()]
param (
)

$queryXml = @'
        <Element>
          <Key>
            <int>-1</int>
          </Key>
          <Value>
            <string />
          </Value>
        </Element>
        <Element>
          <Key>
            <int>3</int>
          </Key>
          <Value>
            <string>sp_WhoIsActive @format_output = 2</string>
          </Value>
        </Element>
        <Element>
          <Key>
            <int>4</int>
          </Key>
          <Value>
            <string>sp_BlitzFirst</string>
          </Value>
        </Element>
        <Element>
          <Key>
            <int>5</int>
          </Key>
          <Value>
            <string>SELECT TOP (100) * FROM </string>
          </Value>
        </Element>
        <Element>
          <Key>
            <int>6</int>
          </Key>
          <Value>
            <string>sp_helpme </string>
          </Value>
        </Element>
        <Element>
          <Key>
            <int>7</int>
          </Key>
          <Value>
            <string>sp_BlitzWho</string>
          </Value>
        </Element>
        <Element>
          <Key>
            <int>8</int>
          </Key>
          <Value>
            <string>sp_WhoIsActive @format_output = 2 , @get_plans = 1 , @get_outer_command = 1 , @find_block_leaders = 1 , @sort_order = N'[blocked_session_count] DESC, [start_time]'</string>
          </Value>
        </Element>
        <Element>
          <Key>
            <int>9</int>
          </Key>
          <Value>
            <string />
          </Value>
        </Element>
        <Element>
          <Key>
            <int>0</int>
          </Key>
          <Value>
            <string />
          </Value>
        </Element>
'@

# Number 20.0 changes with version
$ssmsUserSettingsDirectory = "$env:APPDATA\Microsoft\SQL Server Management Studio\20.0" 
$ssmsUserSettingsFile = Join-Path $ssmsUserSettingsDirectory "UserSettings.xml"
$ssmsUserSettingsBackupFile = Join-Path $ssmsUserSettingsDirectory "UserSettings_backup_$(date -Format "yyyyMMdd_HHmmssfff").xml"
Copy-Item $ssmsUserSettingsFile $ssmsUserSettingsBackupFile 

[xml]$xmlDoc = Get-Content $ssmsUserSettingsFile
$qeSettings=$xmlDoc.SqlStudio.SSMS.QueryExecution;
$queryShortcutsElement = $qeSettings.SelectSingleNode('QueryShortcuts')
if (-not $queryShortcutsElement) {
    Write-Error "Cannot find Query Shortcuts element. Xml Schema might have changed."
    return
}
$queryShortcutsElement.InnerXml = $queryXml
$xmlDoc.Save($ssmsUserSettingsFile)