• SQL Prompt
• SQL Code Snippets

How to use the SQL Prompt snippet placeholders for selecting and copying text

There are four SQL Prompt snippet placeholders that are all about selection and copying of text:

$PASTE$	Inserts the contents of the clipboard at that position.
$SELECTEDTEXT$	Inserts the selected text.
$SELECTIONSTART$	Indicates where you want the start of the new selection of code after you have executed the snippet
$SELECTIONEND$	Specifies the end of the new selection of code after you have executed the snippet

These placeholders are very handy if, for example, you have two blocks of code that need to be part of a snippet. One part you hold on the clipboard, and the other part is a block of snippet text, selected using the $SELECTEDTEXT$ placeholder.

The easiest way of demonstrating how to use them is with some practical examples.

Saving metadata queries as views and TVFs

Imagine that you have a number of queries that you need to turn into inline table-valued functions (iTVFs). I don’t have to imagine too hard because I have pages of metadata queries that I use for exploring the contents of databases.

They are fine as queries, when you don’t want to mess up a database with your code, but during database development work, it is far better to have a schema with all your development code handy. To tidy up at the end, all I’ll need to do is delete the development schema.

The obvious way of storing these sorts of queries is as views, or as iTVFs, if they require parameters. To do this, I’ll create a couple of snippets, ToView and ToTVF. Broadly, they both work like this:

1. Use Ctrl-C to copy the name of the view or TVF, from the corresponding query, to the clipboard
2. Highlight the query and run the relevant snippet, which will capture the selected code into the $SELECTEDTEXT$ placeholder in the snippet
3. Use of the $SELECTIONSTART$ and $SELECTIONEND$ placeholders, in the snippet, will mean that the resulting view or TVF is highlighted and ready to execute, just by pressing F5.

I’ll spin up a copy of AdventureWorks to demonstrate.

Create the snippets

Listing 1 shows the template code for the ToView snippet. The name of the view will be held on the clipboard and used in the $PASTE$ placeholder. I’ve hardcoded the schema name (utils), but you could always use a placeholder for this too.

Notice that I’ve put a $SELECTIONSTART$ at the beginning of the template and a $SELECTIONEND$ at the end, which means that after invoking the snippet, the whole of the substituted text including the test execution will be highlighted. I’ll be able to compile and run the iTVF immediately, by clicking on ‘execute’ or hitting F5.

Listing 1: The ToView snippet

Highlight the code in SSMS, right-click, select Create Snippet, call it ToView, give it a description, and save it.

Then, do likewise for the ToTVF snippet, which is similar and is shown in Listing 2.

Listing 2: The ToTVF Prompt Snippet

If we open the Snippet Manager from the SQL Prompt menu, we see they are now created and saved.

Figure 1: The saved ToTVF and ToView snippets

Turn a query into a view or iTVF

We go to the query pane where we have the code, select the name we want to use for the object and hit Ctrl-C to copy it on the clipboard.

Figure 2: Copy the name for the view, from the query

We now have the name we want for the view, held on the clipboard. Next, make sure you’re connected to the right database, then highlight the query itself, and invoke the relevant snippet, in this case ToView, from the SQL Prompt Action list.

Figure 3: Executing the ToView snippet

We double-click on the name of our snippet to execute it and…now we have the query wrapped up in a view, with everything selected that we need to create and test it.

Figure 4: The code to create and test the view, ready to execute

Just hit F5, or click on ‘Execute’, and we should get this satisfying result.

Figure 5: Executing the TheTables view

A view has been compiled and tested with very little effort. Of course, I could have then clicked (Ctrl K, Ctrl Y) to format it all nicely!

Building a query from SSMS Object Browser

As another example, imagine you are exploring a database and want to work on a table. We need a handy SELECT statement to start exploring that table, and its data. Figure 6 shows a very simple ToQuery snippet that we can use.

Figure 6: The ToQuery snippet

Now, click on the the table of interest, in SSMS object browser, and drag the Columns folder into the query pane, to get a list of all the columns. Then, onto the next line, drag over the table name.

Figure 7: Build the query by drag-and-drop

Highlight the table name, Person.Address, and hit Ctrl-X to cut it, and also capture it onto the clipboard. Then hit Ctrl-A to highlight the remaining column list and invoke the ToQuery snippet from the Actions list.

Figure 8: Execute the ToQuery snippet

Use Ctrl+K, Ctrl+Y (or SQL Prompt | Format SQL) to format the code using the current active style, and then hit F5 or the Execute button.

Figure 9: Results of executing the query

So, we now have another quick way to build a SELECT statement for a table, all correct and nicely formatted. We can develop this into exactly what we need to explore one or more tables.

Summary

I’ve demonstrated just a couple of ways of using these four useful snippet default placeholders. Once you’ve used them as few times, all sorts of other uses will occur to you, so perhaps it is best if I leave it there!

For more detail about how SQL Prompt Snippets and templates work, and how they compare to the standard SSMS versions, check out my earlier article: https://www.red-gate.com/hub/product-learning/sql-prompt/templates-and-snippets-in-ssms-and-sql-prompt.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more