Have you ever spent hours working on a blog post or report to find that your code samples look messy and untidy?

Instead of spending time manually reformatting them, you can now use Format-SQL.com which instantly reformats your code as you type. Format-SQL.com is based on SQL Prompt’s powerful formatting engine allows you to quickly reformat your code and check for errors or invalid syntax. You can then easily copy your formatted code directly, or export it as HTML.

Format-SQL.com is already up and running, but we are planning on adding more functionality over time based on your feedback. We'd love to hear what you use it for and what additional features you'd find useful.

So what are you waiting for? Give Format-SQL.com a try!

 

We’ve just released SQL Prompt 5.3 with some great new features and bug fixes. Most noteable of these is partial matching of suggestions which adds two new types of suggestion to SQL Prompt's code suggestions. 

 You can grab your copy at http://www.red-gate.com/products/sql-development/sql-prompt/.

A full changelog is listed below:

Partial Match Suggestions

•  Partial match suggestions are now enabled for all users – no longer a SQL Prompt Labs feature.

Automatic Refresh Suggestions (SQL Prompt Labs)

•  New SQL Prompt Labs feature to refresh suggestions automatically when SQL Prompt detects a change to a connected database.
• Also refreshes SSMS’s own IntelliSense local cache (equivalent to Ctrl+Shift+R).

Improvements to synonym support (SQL Prompt Labs)

• Basic support for most synonym types, so SQL Prompt notices when the base object is a table, stored procedure, view, function, etc.
• CREATE SYNONYM script is now shown in the object definition box for synonyms.
• Fixed problem caching when base object for synonym is inaccessible (e.g. due to offline database or inadequate permissions).

Bug fixes

•  Fix crash related to scripts containing column list after table alias. (SP-4070)

Other changes

• Addition of MaxSupportedColumns parameter in the engine options, which will prevent SQL Prompt from trying to cache databases with a total number of columns exceeding this number.  Default is -1, which means it’s turned off.  Related to SP-1207.

 

A quick introduction - I'm James Allison, a developer within the SQL Tools division at Red Gate.

Recently I’ve been working on a new experimental feature for SQL Prompt – Auto Refresh Suggestions.

My goal for this feature has been to reduce the number of times users are required to manually refresh SQL Prompt's suggestions. SQL Prompt stores its own internal representation of a database when it first loads it. However, if a change is made to a database after it's been loaded, SQL Prompt's suggestions and expansions may be out of date until you manually select the ‘Refresh Suggestions’ option, as shown below: 

How does it work?

On enabling the new plugin, a background polling thread is started. Every 10 seconds this performs a couple of inexpensive queries (they typically take 10-20ms to complete) on the sys.objects table of the current query window’s connection. The count and the time of the most recently modified object are retrieved. If either of these is found to have changed since the last check, the Refresh Suggestions command is triggered. We also trigger a refresh of SSMS’s own intellisense to update invalid object red squiggles.

In addition to polling, we’ve also hooked into SSMS’s ‘Execute' (F5) command. After this has been called, the plugin immediately checks to see if SQL Prompt needs to refresh its cache.

Work in progress

It’s probably worth stressing that this is still an experimental feature, so in a few cases it may not catch a change. The plugin currently only monitors the connection from the current query window, which means changes to a cached database other than the one being immediately worked on will not be detected until a query window with a connection to it has focus.

Another issue to be aware of is that not all objects are stored in the sys.objects table. DDL Triggers for instance are not schema scoped, and so are found in sys.triggers. As a result, this feature does not currently detect changes to triggers.

SQL Server 2000 unfortunately doesn’t provide a modified date in its sysobjects table, so we check the create date instead. This means far more modifications won’t be detected on these databases.

If the polling thread throws an exception, its current behaviour is for it to log the error as a warning, and then to silently disable itself. This may happen if the current connection is closed or disconnected.

How could it work differently?

I’d very much welcome any feedback you may have on how you’d like this feature to be implemented. Ideally I’d like to keep configuration as clean and as straight forward as possible whilst providing the best balance between performance and accuracy.

Below I’ve listed a number of changes I’m considering. If you have any comments on these, or additional options I’ve not listed, please let us know!

Poll different sources:

The Default Trace: Enabled by default in SQL Server since 2005, the default trace provides a more complete and reliable source of information, but comes with an associated performance cost.

The sys.triggers table: Polling this in addition to sys.objects will enable us to detect changes to triggers.

Polling frequency:

We could poll more often to detect changes earlier – or less often to reduce server load.

Command hooks:

In addition to polling, we also check if we need to refresh whenever the execute command is used in SSMS. This makes many updates instantaneous, but doesn’t cover the other ways you could change the schema in SSMS – such as column renaming from the Object Explorer, or saving changes to a table design. It could be worth hooking into these events too.

Databases to check:

The current behaviour is only to check the database connected to by the current query window for changes. This could be changed to poll all the loaded databases to ensure suggestions are up to date, though this would be a little more expensive.

The latest build of SQL Prompt now includes 'Labs', a set of experimental features you can enable from the SQL Prompt options.

Here's some more detail on the features you can try out:

Partial Match Suggestions

Partial Match Suggestions adds two new types of suggestion to SQL Prompt's code suggestions:

• CamelCase, or compound word suggestions

For example, if you type mt, the object MyTable is suggested:

(This also works on names with underscores, such as my_table)

• Mid-string suggestions

For example, if you type vent, AdventureWorks is suggested:

This is particularly useful if you can only remember part of an object name.

These new types of suggestion will appear below the standard suggestions you are used to in the suggestions list - the order is as follows:

1. Standard (Prefix)
2. CamelCase
3. Mid-string

Tabs Instead of Spaces

This is a simple one - Tabs Instead of Spaces changes the behavior of Format SQL so that it uses tab characters rather than spaces to indent text.

Query Tab Coloring (only in SSMS 2012)

Query Tab Coloring allows you to color query tabs by server connection in SQL Server Management Studio 2012. We blogged about this idea last year, and now we're excited to offer the functionality as a SQL Prompt Labs feature.

Once you've enabled Query Tab Coloring, you can set a color category for a server by right-clicking it in the Object Explorer:

The default color categories are:

Production: Red
Development: Green
Testing: Orange
Staging: Blue

Once you set a color category for a server, each query tab connected to the server will be colored automatically:

Data Explorer

Data Explorer provdes an overview of the data in your database. Once you've enabled Data Explorer, it appears as a new SQL Prompt menu item:

Data Explorer displays rows of data in the tables and views of a database:

Some neat things you can do with Data Explorer:

• filter and sort the data using the upper pane controls
• view the creation script for an object, as well as a summary of its columns by clicking the blue information icon.
• create a SELECT * FROM query for a table or view by clicking ssf.

SQL Tab Magic

We got very excited about SQL Tab Magic, our SSMS add-in that improves tab management, in a blog post back in August last year.

SQL Prompt Labs gives you a convenient download link, so if you haven't already, do try it out.

SQL Test

Again, if you follow the blog you may have already read about SQL Test, Red Gate's new SQL unit test runner for SSMS:

For some more resources to help you get started, see: Getting started with SQL Test.

SQL Test is Red Gate's new SQL unit test runner for SSMS. You can download it here.

SQL Test provides an interface for the tSQLt unit testing framework, allowing you to create and run unit tests against your databases.

Using SQL Test with the example database

After you install SQL Test, it appears when you connect to a server in SSMS asking you to create an example database:

Once the example database (tSQLt_Example) is created, it is automatically added to SQL Test. The example tests are listed in their test class AcceleratorTests:

We can now click Run Tests to run all the example tests. The first example test fails:

To troubleshoot, we can view the test code by right-clicking the test in the list, and then clicking Edit Test.

Creating your own tests

You can add your own databases to SQL Test, and then create your own tests by clicking New Test:

You create tests into test classes. You can create a new class when creating a new test - in the example below, we're creating a test in a new test class WidgetTests:

When a test is created, SQL Test opens a template test query, where you can write your unit test. For more information on writing your own tests, see the tSQLt User Guide.

Feedback

If you have any suggestions, or find any bugs in SQL Test, head over to our SQL Test suggestions forum.

If you're primarily a SQL Server database developer or administrator, then you probably spend your working life inside SQL Server Management Studio (SSMS).  SQL Prompt was designed to work in this environment, and more Red Gate tools have since been written as SSMS add-ins, so that database professionals don't have to switch away so much to perform their tasks.

If you're an application developer, though, things are less clear-cut.  Visual Studio is where you write your application code, but what about that database backend?  Some constantly press Alt+Tab to switch between VS and SSMS, while others attempt to do database development directly inside Visual Studio.  Neither is perfect, but for those who prefer the latter, SQL Prompt (version 4 and later) makes things a little easier by providing the auto-completion and formatting features in Visual Studio when you're editing a SQL file.

Here at Red Gate we have always felt a bit sad that SQL Prompt's Visual Studio support was not as complete as that in SSMS, especially in SQL Prompt 5.0, which added many new features that were SSMS-only.  Until now.  SQL Prompt 5.2 was released recently, and I'm happy to say that all the script-level editing features have now been ported to Visual Studio:

• Uppercase Keywords leaves formatting alone while making SQL keywords consistently uppercase.
• Qualify Object Names checks your script for unadorned object names, and qualifies them with the table and schema as necessary.
• Expand Wildcards finds occurrences of SELECT * in the whole script, and expands them to the full list of columns. 
• Find Unused Variables and Parameters helps you clean up your script by highlighting variables and parameters that can be deleted.
• Summarize Script shows you a summary of your script as a hierarchical tree structure.

These features are enabled in the Professional edition of SQL Prompt.  Note that Qualify Object Names and Expand Wildcards, along with code completion of database objects, require an active connection to a SQL Server instance.

SQL Prompt 5.2 also includes support for the next version of SQL Server, code-named "Denali" (CTP3), and it has a number of stability and performance improvements.

If you are interested in Visual Studio database development, and would like to try an add-in enabling you to work connected to a live database while maintaining source control of your changes, take a look at the early stage release of Red Gate's new tool, SQL Connect.

Using SSMS template parameters in your snippet code can give you a quick way to customize values in SQL Prompt snippets.

Here's a simple example of a snippet with template parameters:

This code selects the top N rows from a table, where the number of rows and the table name have been specified as template parameters, in the format: <parameter_name, data_type, value>.

After we've inserted this snippet into a query, we can hit Ctrl + Shift + M and then enter values for the parameters. So if we wanted to query the top 100 rows from MyTable, we'd enter:

 

SQL Tab Magic is a new tool from Red Gate that improves tab management in SQL Server Management Studio.

You can download an early access release of the tool here.

SQL Tab Magic was built during a couple of Down Tools weeks @ Red Gate HQ, and the chap who built it has written a couple of interesting blog posts about his experiences trying to create and release the tool in a week (spoiler: he didn't quite manage it).

Features

SQL Tab Magic is an add-in for SSMS. Once installed, you'll get some new buttons on your SSMS toolbar:

Recently Closed Tabs

As it says on the tin, this feature shows you a list of tabs you've recently closed:

You can also click a tab in the list to re-open it. This is a potential life-saver if you accidentally close an unsaved query.

Open Tabs

This feature shows you a list of all your open tabs, with a useful preview of their contents. This makes it much easier to quickly scan and find the query you're looking for:

Find Open Tab

This feature allows you to search your open tabs. The search looks both at a tab's title, and its contents:

 

Tip: you can use the handy keyboard shortcut Ctrl+T to quickly open the search box.

Re-open tabs on startup

This feature allows you to restore all the tabs that were open when you last closed SSMS. You can enable/disable this feature from the SQL Tab Magic toolbar:

'Powered by SQL Prompt'

The SQL Tab Magic features are likely to be part of a later release of SQL Prompt, so please try out the early access, and let us know of any issues you find, or suggestions.

So close...

Microsoft introduced connection colouring to SSMS in 2008 but their implementation didn't really solve the problem it set out to; you only get to see the colouring information when you select a tab.

A much smarter idea would be to colour code the tabs themselves so you can see from a quick glance which queries belong to which servers, as sketched out below. This should be useful when working across multiple servers and doubly useful when working across test, dev or production servers of the same database..."don't run that on there!"

This is nothing groundbreaking, tab colouring has been used in various places to improve usability. They have been in browsers for quite some time now--the colourfultabs extension for Firefox has been downloaded over 10M times. Microsoft introduced coloured tabs to Excel in 2002 to boost usability and increase task performance. And more recently, they were added to Visual Studio (via a plugin) allowing programmers to quickly identify different types of file and other properties.

As Denali is going to be based on Visual Studio, i.e. with better plumbing to support modifying tab colours, we were thinking of plugging into this and improving tab usbaility for those peope who spend LOTS of time in SSMS, especially those who have LOTS of tabs open. Colouring tabs according to server, database or simply to mark something of interest all seem like sensible options. If this sounds like something you would be interested in then have a think about these questions and drop us a message or post a comment below.

• How would you expect this to work?
• Do you want to be able to colour code random tabs to make them easier to find?
• Would regular expressions allow you to capture your different server names easily and quickly?
• What would a minimum useful feature include?
• Would you want to share these properties with other members of your team or between the different boxes you work on?

For SQL Prompt 5.1, we’ve tried to make the Find Invalid Objects feature easier to use, and add a few extra neat features. You'll probably notice a few cosmetic differences when you fire up the feature in SQL Prompt 5.1 for the first time, but there are also some more subtle changes.

UX fixes

Firstly, we’ve fixed a few basic usability issues; some we knew about but didn't quite fix in time for the SQL Prompt 5.0 release, and others were gripes reported by users.

1. We've added a progress bar to the UI.

If you're running Find Invalid Objects on a database with only a few objects, this might not make a big difference to you - but it's handy when you're finding invalid objects on larger databases.

2. You can change the server/database to search in from the UI.

Previously, you could only change what you were searching by right-clicking a database in the Object Explorer. You now 'refresh' the results using the new Find invalid objects button. This change also means that the Find Invalid Objects menu item is always enabled, whether you have a database selected in the Object Explorer or not.

3. We now display the database name alongside the number of invalid objects found.

This makes it easier for you to see how many results there are for a particular database.

New functionality

We've gone a step further too, and added some new functionality to lets you do more with the list of invalid objects you've found.

1. You can now script multiple invalid objects as ALTER.

This is made possible by another improvement allowing you to multi-select results:

...so when you click the Script selected objects as ALTER button, you get the objects you selected scripted as seperate ALTER statements in a single query window:

2. You can copy the list of objects to the clipboard.

Just select the objects you want to copy out, and hit Ctrl + C.

3. Double-clicking an object in the list finds it in the Object Explorer.

This little time-saver is particularly useful if you're unfamiliar with the database you're searching. The very short (8 seconds) video clip below shows the feature in action:

Feedback

We hope these changes make finding invalid objects in SSMS more useful for you.

If you've got any further requests/ideas or issues using the feature in SQL Prompt 5.1, please let us know. Many of the improvements we've covered were implemented as a result of user feedback, so we're always keen to hear your comments.