Product articles SQL Prompt SQL Code Snippets
Quick SQL Prompt tip – using the CDB…

18 November 2016
18 November 2016

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:

sql-prompt-cdb-snippet-1

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:

sql-prompt-cdb-snippet-2

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:

sql-prompt-cdb-snippet-3

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:

If you’re not a SQL Prompt user, download a free trial and see how much more productive you can be writing code every day.

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

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more

You may also like

  • Article

    Why your Development team needs SQL Prompt

    While everyone knows SQL Prompt for its code completion and IntelliSense features, a lot of its extra value comes from features that allow the development team to standardize coding practices and drive up code quality.

  • Article

    SQL Prompt Hidden Gems: The SSMS Results Pane

    Every time you need to reuse the query results from SSMS, for example to populate another table, or to search for matching rows in another table, it will inevitably mean a lot of manual tweaking to the get the results into the right format. Louis Davidson uncovers three hidden Prompt gems (Open in Excel, Script as INSERT, Copy as IN clause) that can remove all this pain.

  • Article

    When to use the SELECT…INTO statement (PE003)

    SELECT…INTO is a useful shortcut for development work, especially for creating temporary tables. However, it no longer has a clear performance advantage and should be avoided in production code. It is better to use a CREATE TABLE statement, where you can specify constraints and datatypes in advance, making it less likely that inconsistencies will sneak into the data.

  • Article

    Insert Statement Without Column List (BP004)

    Many production databases have failed embarrassingly as a result of INSERT code that omits a column list, usually in mysterious ways and often without generating errors. Phil Factor demonstrates the problem, and advocates a 'defense-in-depth' approach to writing SQL, in order to avoid it.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly