The GUI Helps Build SQL Prompt Snippets

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool.  SQL Prompt will be yours as well if you give it a try.

SQL Prompt is an amazing tool for writing T-SQL. It helps you to concentrate on your code, without having to go off and examine Books Online or other documentation, or even worry about formatting.

One of the keys to making Prompt efficient for you, in your environment, is to use Snippets. These are short codes that insert a lot of T-SQL, like the SSF snippet. As I’ve worked to make myself more efficient, I’ve also tried to be efficient in my creation of snippets. One way to do that is using the SSMS GUIs.

For example, suppose I want to build a snippet for databases. I can open the Create Database dialog. This has the places where I can specify the name, owner, size, growth, options, etc. You can see the main page below.

2016-09-23 10_09_59-Posts ‹ Voice of the DBA — WordPress

Once, I’m happy, I click the “Script” button at the top, as shown here.

2016-09-23 09_48_24-New Database

This gives me the code in a window, where I can then customize for my template parameters and then paste in the snippet.

The same thing for logins and users. I create and drop a lot of security princpals, so I can right click on logins, as I have here in the Object Explorer.

2016-09-23 10_11_35-SQLQuery1.sql - (local)_SQL2014.Sandbox (PLATO_Steve (68))_ - Microsoft SQL Serv

I’ll get a New Login dialog, and I can add in the items that matter, like defaults, password, types, etc.

2016-09-23 10_12_12-Login - New

When I now click the Script button, I get my code. Note, I’ll cancel out of the dialog, and work with the code shown here.

2016-09-23 10_14_34-SQLQuery3.sql - (local)_SQL2014.Sandbox (PLATO_Steve (61))_ - Microsoft SQL Serv

I can cut and paste this into the Snippet edit box. In the image below, I’ve deleted the old code for cl (“Create SQL Server login”) and put my code in. I change the specifics (username and password) to template parameters by putting a name in between two dollar signs ($). This will give me a quick way to customize the snippet in each case.

2016-09-23 10_15_10-SQL Prompt - Edit Snippet

Note that I need to replace each instance of “JohnDoe” with “$username$” for this to work well. I did that before I saved the snippet, as well as added a template for the database.

Now when I type “cl”, I get the snippet.

2016-09-23 10_19_13-ObjectDefinitionBox

and hitting Tab gives me the code.

2016-09-23 10_19_22-SQLQuery3.sql - (local)_SQL2014.Sandbox (PLATO_Steve (61))_ - Microsoft SQL Serv

That’s a quick and easy to add Snippets for those items you perform often with the GUI in SSMS. You will code quicker and more consistently, and you’ll create new objects without thinking.

Hopefully, you’ll see the value in SQL Prompt and start using snippets to improve your ability to code quickly and take the hassles and guesswork out of cleanly building SQL Code. You can also read a similar piece I wrote on the Redgate blog.

Try a SQL Prompt evaluation today and then ask your boss to get you this productivity enhancing tool, or if you’re using the tool, practice using ii the next time you need to insert some data.

You can see a complete list of SQL Prompt tips at Redgate.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.
%d bloggers like this: