Handy SSMS Query Shortcuts

In SQL Server Management Studio, you are able to define custom query shortcuts to help with common tasks. I have been using them for quite some years now and realized I haven’t written about them yet. So here we go.

The query shortcuts can be found under Tools -> Options... -> Environment -> Keyboard -> Query Shortcuts and the default setting doesn’t look very exciting.

ssms_keyboard_query_shortcuts

Before I go any further, let me explain how query shortcuts work. The “shortcut” is obviously the key combo you press which does two things. It (1) prepends the “statement” to the highlighted text in the editor and (2) executes it. Although SSMS calls the statement Stored Procedure but it is not limited to only procs, it works with any valid T-SQL statement. In other words:

shortcut = statement [highlighted text]

Let me show you with an example, let’s say you have a query that looks like the one below and you would like to run sp_help sys.columns to see the table details:

select *
from sys.objects a
join sys.columns b on a.object_id = b.object_id

Without a shortcut, you’d need to go to a new line and type the command. Now with the help of the shortcut, you can simply select the text sys.columns and press Alt+F1 to achieve the same result. How cool is that? I hear you want more. Ok, I will reveal all the query shortcuts I find particular useful.

sp_helptext – How many times have you looked at a view or proc and wondered about the implementation underneath. Well, no need to search for that object in Object Explorer anymore, simply setup a shortcut. My personal preference for this shortcut is the Ctrl+F1 slot.

select * from sys.objects where name like – This is quite handy when you want to quickly check to see if an object exists in the database. I normally set this to Ctrl+5

select – Don’t let the simplicity of this shortcut fool you. Not having to type those six characters can save a lot of time when you want to run a system function embedded in a query, such as suser_sname(), newid(), getutcdate() etc. I normally set this to Ctrl+6

select top 10 * from – The reason for having a separate top 10 query shortcut from the select all is that it provides a quick and easy way of retrieving the column names with some sample data. I normally set this to Ctrl+9

select * from – I use Ctrl+0 for this.

The shortcuts listed above are the common ones I use almost everywhere whereas the ones below are less common but still handy to have around:

select concat(””, newid(), ””) as [newid()] – If you have a need to generate GUIDs all the time, then surely it deserves a shortcut slot.

set statistics io, time on – This one is for folks looking at performance.

select * from dbo.dbversion order by id desc – This is useful if you have a version table in the database. Setting up a shortcut can make it easier for checking versions.

Code snippets in SQL Server 2012 for dummies

Normally I don’t write about a topic if resources are widely available on the web. Now knowing how useful code snippets are, I was a bit surprised at the lack of good instructions on the topic. In the past, I had access to Red Gate at work so I was blessed with the power of SQL Prompt snippets. Gone are the days. I eventually dived in and learnt to use the Code Snippets Manager that comes with SQL Server Management Studio (2012). It involved some messy XML but at the end, it wasn’t too hard at all.

In this post, I am going to walk through one of my snippets and try to explain what everything means to the best of my knowledge:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets>
 <CodeSnippet Format="1.0.0">
 <Header>
 <Title>sp_msforeachdb</Title>
 <Shortcut></Shortcut>
 <Description></Description>
 <Author>Louie Bao</Author>
 <SnippetTypes>
 <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
 <Code Language="SQL"><![CDATA[
set nocount on
set transaction isolation level read uncommitted

if object_id('tempdb.dbo.#results') is not null
 drop table #results

create table #results 
(

)

insert #results
exec sp_msforeachdb 'use [?]
if db_name() like ''''
begin

end'

select * from #results
]]>
 </Code>
 </Snippet>
 </CodeSnippet>
</CodeSnippets>

The first thing you’d notice with the xml above compared to one of the examples found on Books Online is that that I have removed quite a few things. This is what I found the easiest way of creating code snippets.

<Title>

This is the name you will see in the list of snippets. Make sure it is named properly so you can find it later.

<Shortcut>

Initially I thought it was the keyboard shortcut to invoke the snippet like how it is done in Red Gate’s SQL Prompt snippets. Turned out it isn’t all that useful as you still need to go through the Ctrl + K + X sequence to get to it. That’s why I left it blank.

<Description>

Optional. Common sense applies here.

<Author>

Again optional, nothing special, just put your name here.

<SnippetTypes>

As far as I know, there are two snippet types: Expansion and SurroundsWith. SSMS uses this value to determine which context menu will be used to display the code snippet.

Expansion        = Edit -> IntelliSense -> Insert Snippets… Ctrl + K, Ctrl + X
SurroundsWith = Edit -> IntelliSense -> Surround With… Ctrl + K, Ctrl + S

I choose to create all my snippets as the Expansion type. It is just so much easier to understand and work with as I don’t need to worry about things like the $selected$ and $end$ tokens. One of the key reasons I decided to go with Expansion is that I just can’t seem to find a list of all these predefined tokens in order to take advantage of the SurroundsWith type. Furthermore, using SurroundsWith means I need to learn about all the silly tags like

<Declarations>
<Literal>
<ID></ID>
<ToolTip></ToolTip>
<Default></Default>
</Literal>
</Declarations>

and it is just simply unneccessary.

<Code Language=”SQL”>

Self explanatory. Since this is a Visual Studio technology I assume you can have other languages like C#. For my purpose, a snippet is a snippet, SQL will do just fine.

<![CDATA[ code snippet ]]>

You don’t have to be an XML expert to figure this out. The actual code snippet lives inside this tag.

Demo

As I have shown above, that’s all the XML you’ll need to know to create a code snippet file. Next I will show you how it all ties together.

1. Save the code snippet file as sp_msforeachdb.snippet in the %userprofile%\Documents\SQL Server Management Studio\Code Snippets\SQL\My Code Snippets\ folder which should already exist. Once done, the file is immediately visible in SSMS (Tools -> Code Snippets Manager…, look inside My Code Snippets) and any modifications done to the file thereafter will be reflected immediately in SSMS. How cool is that! By the way, notice how I haven’t done anything fancy to Add… or Import…, all I did to make the snippet available was by saving a file.

2. Invoke the code snippet with the Ctrl + K + X key sequence.

code_snippet_quick_access

The screenshot above shows the code snippet I have saved earlier. Some of you may notice at a glance that there are something missing from the list, yeah, all the default Microsoft code snippets! That’s because for faster access, I have removed (hidden) all the default ones that came with SQL Server 2012 by renaming…\Tools\Binn\ManagementStudio\SQL\Snippets\1033 to 1033 (Hidden).

3. Complete the query by filling the gaps.

set nocount on
set transaction isolation level read uncommitted

if object_id('tempdb.dbo.#results') is not null
	drop table #results

create table #results 
(
    name sysname
)

insert #results
exec sp_msforeachdb 'use [?]
if db_name() like ''louie''
begin
    select name from sys.objects where type = ''u''
end'

select * from #results

–oo00oo–
For more info, please refer to the links below:
Investigating Code Snippet Technology

How to remove double spacing after pasting code

If you copy and paste T-SQL code around frequent enough, I am sure you would have seen the double spacing issue.

For example:

select name

from sys.databases

where database_id = 1

order by name

The quickest way to remove double spacing is by using the “Find and Replace” function found in SQL Server Management Studio (SSMS):

  1. Find and Replace: Ctrl+H
  2. Find what: \n\n
  3. Replace with: \n
  4. Find options: Use Regular expressions
  5. Replace All: Ctrl+A

image

Bob is your uncle.

GO GO GO

The GO command since SQL Server 2005 takes a [count] argument which instructs the server to execute the batch preceding GO the specified number of times. I have never realised that until yesterday.

So instead of doing:

declare @i int
select @i = 0

while @i < 100
begin
    insert test default values
    select @i = @i + 1
end

I can just execute:

insert test default values
go 100

Simple and elegant.