My Top 10 Most Common Database Scripts article described the top 10 T-SQL commands, as voted for by the SQLServerCentral community, which as a DBA you will find yourself tapping into your keyboard, day in day out. You’ll use some of these commands so often that they’ll virtually write themselves on your screen, but if you’re a keyboard dyslexic and also lazy, like me, then familiarity quickly breeds contempt for repetitive typing, and you will soon be looking for a way to run these commands with one or two keystrokes instead of twenty.

In this article, I’ll show how to use Redgate SQL Prompt to make that happen. I’m going to create snippets for each of the Top 10 T-SQL commands to see how I can make these commands much easier and faster to run on your systems.

I’ve exported my snippets (see the Download link, above) so that you don’t have to even retype what I’ve done.

#1 sp_who2 / sys.dm_exec_requests / sp_whoisactive

The first script for the SQL Prompt Snippet treatment is the one that interrogates a few Dynamic Management Objects and shows you what’s currently running on the system, the query text and the execution plan. Most of the code trips off your fingers after a while, though the section that parses the statements remains a little trickier. We can take away this pain by creating a snippet and giving it the command rn, for ‘right now‘, as shown in Figure 1.

Figure 1

Next time you need to run it, simply type rn, select the snippet and SQL Prompt will present the entire query for you, so you can see in a flash what is currently running on your server.

#2 STATISTICS IO/TIME

Typing STATISTICS IO doesn’t take much time, you might think, so surely any time saving is going to marginal? In fact, setting both commands ON, and then OFF again, gets dull pretty quickly, plus SQL Prompt has some built-in functions, called placeholders, that can help out even further.

Figure 2 shows the code for my new snippet, ssit

Figure 2

$SELECTEDTEXT$ and $CURSOR$ are placeholders. The former allows you to grab the currently selected text within the query window and use it with your snippet, and the latter is a marker for the cursor position, when the snippet completes, in case you want to type some additional code before you run your query.

To see this in action, simple highlight some code and look out for the little SQL Prompt icon to the left (introduced in SQL Prompt 7).

Figure 3

Hit the control button once and let go to open the dropdown for this icon and you’ll see a list of commands and functions that you can execute against the selected code. Type in ssit to select the new snippet and hit enter to apply it. That’s just six key strokes to collect execution IO and timing statistics for any highlighted piece of code compared to about 32 with manual typing.

#3 BACKUP DATABASE

For the BACKUP DATABASE command, I’m going to create a snippet that allows me very quickly to create and run a database full backup that uses the WITHCOPY_ONLY clause.

Figure 4 shows the code for my bu snippet. You’ll just need to adjust the file locations as appropriate for your system.

Figure 4

This time, instead of using one of the built-in placeholders, I’ve created my own, called $DatabaseName$. This allows me to do something fun. I’ll type bu in a query window and hit space to fill in the code behind the snippet (I adjusted the default behavior so that the spacebar activates SQL Prompt on my machine).

Now for the fun part: SQL Prompt automatically highlights the placeholder, and presents a list of available databases on the SQL Server 2014 instance to this session is connected.

Figure 5

All I have to do is type a, and the AdventureWorks2014 database will be highlighted. I hit space again, it gets selected. That’s not all. Look what happens to the rest of the code…

Figure 6

That’s right. Each of the placeholder locations has been replaced with the database name that I selected. I’ve got my backups running faster than ever and I don’t have to retype the name or copy and paste to get things right.

#4 sp_help

SQL Prompt already provides a built-in a snippet for running sp_help. Simply type sph and it will do the rest. I even edited my sph snippet so that it puts a semi-colon as a statement terminator.

#5 DBCC SQLPERF

Nothing special needed for this one. I created a new snippet for running DBCC SQLPERF(LOGSPACE). However, for really short commands like this, it’s almost faster to skip the snippet and just type them out. Using SQL Prompt’s auto-complete, I can type out the command in 9 keystrokes, so I’m only saving 6 keystrokes. Since we’re talking either 3 or 9 keystrokes out of a total of 24, it’s still a win.

Figure 7

#6 sys.dm_exec_query_stats

There’s a ton of typing to be done to put together a good query against then sys.dm_exec_query_stats DMV, in order to return aggregated query statistics plus the SQL text and execution plan for previously executed queries that have plans in the plan cache. As a nice partner for the previous rn snippet for sys.dm_exec_requests, this one’s called nrn for ‘Not Right Now‘.

Figure 8

The only little addition here is the $CURSOR$ placeholder that upon running the snippet leaves the cursor in the right location to just keep typing, and add the appropriate WHERE clause for the query.

#7 RESTORE DATABASE

There are so many different options for running the RESTORE that it’s not easy to come up with a general-purpose snippet. Here, I’ll assume you simply want to restore a database from the full backup that results from #3, replacing the existing database. I’ve simplified the RESTORE options here, and you would need to adjust these on your systems depending on requirements. Here, I assume this is a simple restore of a full database backup for your development environment, and possibly from a secondary database. In such cases, Figure 9 shows a very simple snippet you could use.

Figure 9

I’ve reused the custom $DatabaseName$ placeholder so that I can quickly supply the right name and have it used in all the appropriate places. I’ve also made sure that I have my RECOVERY script ready to go as well. This does save tons of typing.

#8 RESTORE FILELISTONLY

Again, RESTORE FILELISTONLY is fairly simple to type in, but I can still save myself some time.

Figure 10

I don’t know about you, but I seldom type full path statements, especially the UNC path. Most of the time I’ll navigate to the folder so that I’m sure where it is and then I’ll copy the path from the Explorer window.

I take advantage of that with this rf snippet. The built-in placeholder, $PASTE$, will capture the last item copied to your clipboard, which in this case will be the full path and file name and the relevant backup.

#9 sp_spaceused

sp_spaceused is an excellent candidate for a snippet with some custom placeholders. Figure 11 shows how I’ve set this one up.

Figure 11

Note the additional wrinkle in Figure 11; I’ve taken advantage of the Default Value setting and entered a default of true for the @UpdateUsage argument, meaning that generally this command will rescan the system views so that they report up-to-date size and space information. However, it’s still easy to override and set it to false in cases where it’s not necessary.

Figure 12

I didn’t really have to use a placeholder for the @objname entry. Instead, I could have just ensured that the cursor dropped there between the quotes and left it to you to start typing. However, I like putting the parameters in order, so I have you set the @objname and then @updateusage. It’s a choice.

Speaking of which, you’ll note that when you start creating placeholders, the order you create them in is the default order in which they will be applied. However, you can change the order by using the arrows in the SQL Prompt Snippet Manager GUI to move them up or down.

#10 DBCC SHOW_STATISTICS

Finally, we have DBCC SHOW_STATISTICS and the snippet is very straight forward.

Figure 13

Nothing special this time; I just set it up the way it made sense, with custom placeholders for the table name and the name of the set of statistics (i.e. the column or index name).

Conclusion

I’ve been using SQL Prompt for a very long time now and it just keeps getting better, especially with all the new functionality around placeholders. This article demonstrated how to turn our ‘Top 10 Common T-SQL Commands’ into snippets that makes those commands much easier to implement within your systems.

SQL Prompt is available as a free 28-day trial, download it today and give it a try.