Product articles SQL Prompt SQL Code Analysis
Exploring New SQL Prompt Features in…

Exploring New SQL Prompt Features in SQL Toolbelt and SQL Toolbelt Essentials

Roseanna Whitelegg explores new SQL Prompt functionality that has been added to SQL Toolbelt and SQL Toolbelt Essentials to help teams increase their productivity, improve code quality, and make collaboration easier.

If you own SQL Prompt as part of a Toolbelt (TB) or TBE Essentials (TBE) license, then you’ll have access to additional features designed to improve team-based development. Firstly, bulk actions, including bulk formatting and bulk code analysis, enable teams to retrofit a SQL formatting standard to an entire legacy code base, and quickly review the quality of a code base.

Secondly, SQL Prompt is being more closely integrated with other tools in TB and TBE, starting with SQL Dependency Tracker, to allow developers to see both “used by” and “uses” dependencies of a database object within SQL Prompt. Finally, teams with a TBE subscription license have additional access to the Redgate Platform, a cloud hosted service where teams can store and share SQL formatting styles as well as SQL code snippets. This makes it much easier for them to implement a SQL formatting standard across the team, as well as to promote reuse of standardized code.

Bulk actions

SQL Prompt’s bulk actions help teams to save time and improve code quality by applying formatting and code analysis to an entire code base in one go. The bulk formatting and bulk code analysis options are available from the Bulk Actions menu item (in the SQL Prompt menu) or via the Command Palette (accessed by pressing ALT+S).

Bulk formatting

As soon as you open a SQL file in an SSMS tab, you can use SQL Prompt’s Format SQL command (Ctrl K+Y) to format it to your preferred style in seconds. However, what if you need to format and review all the code modules in a database? Or you’ve changed the team policy on how you wish to format SQL code? It will take a long time to reapply your preferred style, tab-by-tab, file-by-file. A better approach, to reformat all the code in the database, might be to generate and format a build script then use SQL Compare to apply the formatting changes back to the database.

However, with bulk formatting, you can apply a formatting style to multiple SQL files, or All Tabs (meaning all open tabs in SSMS), in seconds.

SQL Prompt Bulk Actions menu

If you select Format SQL Files, a dialog box will appear with its options automatically populated based on the current active style in SSMS, and on the Prompt formatting actions that are set to run automatically with Format SQL. You can change the settings here without affecting the default settings (the ones saved in Options | Styles). By default, SQL Prompt will save backups of the formatted files.

Select the directory of scripts you wish to format, select which actions to apply, including the style in which you want all files formatted (or leave them all at their current settings), and hit Format:

Configuring settings for bulk SQL formatting

Progress will be shown and once finished any files that could not be formatted, due to syntax errors, will be summarized:

Applying bulk SQL formatting

Bulk formatting can also be applied as part of an automated process, using the command formatter. This feature is covered in full detail in a separate article, Bulk Formatting of the SQL Server SQL Files. However, as a quick example, a bulk formatting command might look like this:

C:\Program Files (x86)\Red Gate\SQL Format>sqlprompt.format.commandline.exe --i-agree-to-the-eula --path C:\MyPath\Documents\Githhub\MyDatabase\SQL  --style Default -e WithoutBrackets -b

Bulk code analysis

With bulk code analysis, you can analyze your entire code base at once. This might be especially handy when you’ve inherited a legacy database and need to get an initial feel for the extent of its ‘technical debt’. You can also opt to fix any issues that crop up using the bulk auto-fixing of code analysis issues.

Select Bulk Actions | Code Analysis, select the directory of files you wish to analyze (it can contain sub-directories), choose if you want to apply auto fixes, and to create backups of the fixed files, and then hit Analyze.

Graphical user interface, text, application Description automatically generated

It will run all the code analysis rules currently enabled in your CodeAnalysisSettings.casettings file and display all violations:

SQL code smells uncovered using bulk code analysis

Integration with SQL Dependency Tracker

The SQL Dependency Tracker integration enables you to look up your database object dependencies within SQL Prompt via the Object Definitions dialog box, so you can quickly assess the impact of any changes you plan to make. In the SSMS query pane, just hover over an object and click on the highlighted definition:

viewing an object definition

This will open the Object Definitions box and you can see all the object dependencies in the Dependencies tab:

Viewing an object's dependencies in SQL Prompt

You can also get the dependencies “as-you-type” from Prompt’s suggestions box, as it provides auto-completion suggestions for the columns and tables you wish to access.

Sharing styles and snippets via the Redgate Platform

When a developer shares a SQL Prompt formatting style or code snippet, it is automatically uploaded to a ‘Team space’ on the Redgate Platform and instantly made available to any other SQL Prompt users who have access to that space. Any subsequent changes to shared items are automatically synchronized with the Redgate Platform.

This provides a simple, centralized mechanism for teams to define, share and maintain a single set of team ‘rules’ or standards for certain aspects of their code design and code formatting. For example, team collaboration, such as during code reviews, becomes much more efficient if all SQL code adopts the same formatting standard. Code quality can be improved, and duplicated effort avoided, if the team can share and reuse standardized code snippets, such as for testing code modules, or handling errors or for documenting migration scripts.

So, how does the Redgate Platform differ to the standard way of sharing snippets and styles, using a shared folder? As the functionality is accessible from within SSMS, it streamlines the process of sharing items. It also allows team leads to easily restrict who can edit, upload, and change shared items with in-built role-based access control (RBAC). Finally, this is just the beginning for the Redgate Platform – the development team is actively working on new capabilities to better integrate SQL Prompt with other tools.

Set up the team space

Simply navigate to the SQL Prompt menu in SSMS and select Log in to Redgate Platform. You will be asked to choose a profile – either Team Admin or Team Member.

Before sharing can begin, a member of the team will need to log into the platform as a Team Admin, using their Redgate ID. The Team Admin can create the team space in the Redgate Platform, where all shared items will be stored. Once the space is created, a Team Admin can then invite other users to join, usually as Team Members, assigning each user either read (Reader) or read-write (Editor) access to shared styles or snippets or both:

Setting up a team sharing space on Redgate platform

Having set one or more team spaces on the Redgate Platform, and with all users assigned to the relevant team, you can start to share styles and snippets, in SQL Prompt.

Share SQL formatting styles

SQL Prompt’s shareable SQL formatting styles give teams both the discipline of adopting a “house style”, at the point that code needs to be shared with others, and the freedom to adopt personal styles when in a private workspace.

Within SQL Prompt, every user will have access to their personal styles and to the built-in Redgate styles, stored in a local folder, and to any styles shared via the Redgate platform. It is very simple and fast to switch between and apply different formatting styles (see, for example, Using multiple custom SQL formatting styles in SQL Prompt).

To share a style with other team members, select Edit Formatting Styles… from the SQL Prompt menu, or the Command Palette. Press the ellipsis menu button next to the style you wish to share to open the context menu and then choose the Share this style (or you can click on a style to highlight it and use the Share style button at the bottom):

Sharing a formatting style

Select one of the team spaces you created on the Redgate platform, and hit Share style, and the style will be synchronized with the Redgate Platform and made available to all members of that team, when working in SQL Prompt:

Using a shared style for development work

Share SQL Code Snippets

Shareable SQL code snippets allow the team to introduce standards and consistency to the way the team build objects and code modules, such as in terms of how they handle errors, or are documented. It also allows them to reuse, rather than individually ‘reinvent’, standardized modules for tasks such as testing code or investigating slow-running code.

Sharing SQL code snippets via the Redgate platform is just as straightforward and can be done from within the Snippet Manager, accessible via the SQL Prompt menu or the Command Palette.

To share a snippet, select the snippet and press the share button at the top of the window. Again, you will be asked to choose a team space within the Redgate Platform and within minutes the snippet will be ready for team members to access.

Sharing a SQL code a snippet for reporting query execution times

Shared snippets will appear separately in the snippet manager, and when searching for a snippet you can use the filter to limit your search to shared or personal snippets.

Using shared code snippets

Summary

This additional SQL Prompt functionality, available with a TB or TBE license, will help teams get even more value from the tool and boost collaboration. SQL Prompt’s bulk formatting action makes it easier for teams to standardize their code by allowing them to apply a company style to the entire code base. The bulk code analysis and bulk auto fixing of analysis issues actions enable teams to quickly review and improve the quality of their code. The SQL Dependency Tracker integration facilitates due diligence by allowing teams to check database object dependencies from within SQL Prompt. Finally, the Redgate Platform, available with a TBE subscription license, improves team collaboration by providing an easy way to store and share SQL formatting styles and snippets.

 

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more