Quick SQL Prompt tip – using the CDB snippet
SQL Prompt includes a number of snippets by default that can help you quickly write T-SQL code. These are templates of code that users use regularly. One of the more popular snippets is the ‘cdb’ snippet that helps with quickly creating a new database for development work. This can also be used to ensure production databases are created with the standard settings that your organization may have.
Let’s examine how this snippet works. If I type cdb, I get the popup with this snippet:
The code for the snippet is shown to the right, and as you can tell, it’s a lot of code. Three characters and I’ve saved over a hundred keystrokes when I type TAB. Once I do that, I get the code in my window, but I also get my cursor at the point where the database name would be typed:
This is another feature of SQL Prompt. The parameters that are in the snippet can be placed in multiple places. In this case, the database name is also used for the virtual and physical file names. I’ll type QuickTest and then TAB. I now see this:
If you look through the code, you’ll see I have the database name included in the virtual file names (quicktest_data and quicktest_log) as well as the filenames (quicktest.mdf and quicktest.ldf). I would certainly add additional filegroups/filenames if I wanted by editing the snippet. I could also easily uncomment out some of the database settings if they were appropriate for my test databases.
At this point, I could execute this snippet and have a new database that allows me to test some code. Four keystrokes produced all this code.
There are a number of built-in snippets for SQL Prompt, and many of them are very simple, but they can greatly speed up your T-SQL coding if you learn what they are. I’d suggest you download our SQL Prompt Quick Reference Guide and practice using a few of these shortcuts in your code.
I also cover a few other snippets including SSF (SELECT * FROM) and II (INSERT INTO) in the new series of #SuperSQLTips videos. Take a look and you’ll find handy tricks from other Data Platform MVPs and SQL Server experts too, not just on snippets, but a host of tips and tricks to help you write SQL more quickly and accurately:
Like this? Share it with other SQL professionals using the social sharing buttons below. Simply tweet: I’ve just read @way0utwest’s blog on using the CDB snippet with #SQLPrompt – see the #SuperSQLTip at http://rd.gt/2fWiZrs
Tools in this post
You may also like
-
Article
Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8)
If you declare a variable-length string , or coerce a string, without specifying its length, you can fall foul of ‘silent’ string truncation. Some developers resort to using the
(MAX)specification, which is a mistake too. Phil Factor explains the dangers and then offers a workaround for the problem, when you're importing text and simply don't know the correct length of each string. -
Live training session
What’s new in SQL Prompt
As a Redgate customer, you are invited to attend this exclusive training session to discover SQL History and how you and your team can accelerate your coding and standardize development. In this 30-minute training session, Grant Fritchey, Redgate Advocate, will demonstrate how SQL History works, provide an overview of SQL Prompt and allow time for Q&A
-
Article
Tracking use of Deprecated SQL Server Features
Phil Factor explains how to use Dynamic Management Views and Extended Events to track use of deprecated SQL Server syntax on working SQL Server databases, as well as SQL Prompt and SQL Change Automation to detect its use during database development.
-
Article
How to Apply Non-Standard SQL Formatting Using SQL Prompt
SQL Prompt not only will format your code exactly as you want, but will also help you switch quickly to an alternative style, or to apply exceptions to certain parts of a SQL script, where your preferred style isn't what's required.
-
