What I Do
I work as a lead information architect on a large government health care services integration project and you might say I live in SQL Server Management Studio (SSMS) and Visual Studio (VS) during my work hours. I’m also a fan of Redgate’s tools and today I’d like to share some of the love I have for SQL Prompt. I love tools that save me and my team time which ultimately translates to saving money for my employer and I’ll proceed to show you how SQL Prompt can do that for you.
What SQL Prompt Can Do For You
I’m working with version 7 of SQL Prompt which can be downloaded for a free 28 day trial here: http://www.red-gate.com/products/sql-development/sql-prompt/
Major features provided by SQL Prompt are:
- IntelliSense-style code completion
- Customizable code formatting
- Code snippet library
- Refactor SQL code
- SSMS tab history
- SSMS tab coloring
IntelliSense and Formatting
Let’s begin with IntelliSense. This feature is a vast improvement over the out of the box experience you get with SSMS and VS. As you can see in the figure below, there is adequately more information present in the SQL Prompt pop ups.
I love the way suggestions work, and you can filter that behavior using the drop down list at the bottom of the first popup in the figure above. I started the example query using the * wild card for columns so I could show you one of my favorite features. Placing the cursor at the end of the * allows you to hit tab and expand the column list as seen below:
From there, I can easily edit the column list and save tons of time when dealing with tables that have a large number of columns often used in queries. While we’re working with this query, I also want to show you how easily you can change the format. Just highlight the SQL and right click to make your query look great!
Properly formatted SQL is easier to maintain, and SQL Prompt allows you to have a lot of control over how you prefer the formatting to work.
Snippets
SSMS has supported templates for several versions that allow you to reuse boilerplate scripts to easily create objects in your databases. SQL Prompt takes this concept further by implementing the code snippet library. There are many snippets already built into SQL Prompt and activating a snippet is as easy as typing the snippet definition like “st100” to select the top 100 rows from a table as shown below:
It’s easy to highlight an existing query and turn it into a snippet using placeholders. Built in placeholders are reserved words that are variables for specific pieces of information or they represent actions that can be applied to your snippet. To get a feel for how they are structured, simply open up the snippet manager and look over the definitions of existing snippets. You also have the ability to create custom placeholders which are basically free text fields. Alasdair Daw has a wonderful article here that examines the subtleties in this great feature: http://www.red-gate.com/blog/snippet-manager
The ability to create snippets is really useful for repetitive tasks, but before you reinvent the wheel, be sure to check out Gaurav Vohra’s repository of useful snippets on Git:
https://github.com/gvohra/sqlpromptsnippets
I mentioned SSMS templates earlier and want to note that you also have the ability to use those template parameters in SQL Prompt snippets. The nice folks at Redgate have an excellent walkthrough here:
http://documentation.red-gate.com/display/SP7/Using+SSMS+templates+in+SQL+Prompt+snippets
Refactoring
The ability to refactor code is one of the most valuable features of any toolset. I often find myself working on large legacy systems that employ poor, or in most cases, no naming conventions or consistent coding standards. I mentioned earlier when showing off the formatting features of SQL Prompt that well formatted code is easier to maintain thus leading to saving time and ultimately money.
SQL prompt has many amazing refactoring features starting with Smart Rename which allows you to right click and rename objects without breaking dependencies. This makes it easier to apply a naming standard to existing tables, views, stored procedures and functions along with their respective column names and parameters. You can also rename scripted variables.
I’ve long been a proponent of using stored procedures in my database projects, and SQL Prompt supports the ability to highlight any block of valid SQL and encapsulate it into a stored procedure.
The system I’m currently working on started with our team inheriting a legacy system. By using the aforementioned features, this allowed us to properly name and format the scripts and database objects in a timely fashion. We also had the opportunity to greatly improve the normalization of the database which required splitting several existing tables into two or more tables. SQL Prompt came to our rescue with support for that very action:
http://documentation.red-gate.com/display/SP7/Splitting+a+table
Tabs
Tab history (SQL 2008 or later) is a nice feature that stores the list of tabs you’ve opened and allows you to easily get back to them along with the ability to rename or delete them from the list. This may seem simple, but as I get older I sometimes forget where I parked at the mall so it is helpful for me to have that historical list handy.
I’ll wrap up my tour of SQL Prompt features with the new addition of colored tabs (SQL 2012 or later). This allows you to select different colors for tabs to represent different servers or database connections as shown in the figure below:
As you might imagine, this feature is a godsend when you have to work in different environments and need to stop the madness of running a script in the wrong environment. This is another simple feature that eliminates confusion with support for coloring at the database and server group levels and support for wildcard matches. You can read more about this great new feature here: https://documentation.red-gate.com/display/SP7/Coloring+query+tabs
Integration With Visual Studio
Just about all the great features we’ve looked at and more are also available in VS. As you can see from the figure below, SQL Prompt adds a menu entry in the VS development environment just like we have in SSMS as well as additional entries when you access a popup menu.
Conclusion
I’ve been using Redgate tools for over 10 years, and the Redgate team has done a fantastic job with SQL Prompt. The SQL Developer Bundle, which includes SQL Prompt, was one of the first tools I purchased for my team of database administrators and architects when I started my current project. I can honestly say the tools have saved us hundreds of man hours and paid for the purchase many times over.
You can read more about the return on investment with SQL Prompt in this excellent article: https://www.red-gate.com/assets/INTERIM/assets/products/sql-prompt/documents/sql-prompt-roi-guide.pdf. This product is free to try for 28 days. Just follow this link: http://www.red-gate.com/products/sql-development/sql-prompt/
