dbForge SQL Complete 7.0 Just Released

If you work with SQL Server, then you'll be interested in this.

dbForge SQL Complete, an add-in for SSMS and Visual Studio, has just been released with a new version and some great new features.

In this post, you'll learn:

• what these new features are
• how they can help you with your database work
• how to access them

Let's get started.

Disclaimer: This post is sponsored by Devart, who are the creators of dbForge SQL Complete.

Getting Started with dbForge SQL Complete

dbForge SQL Complete is a part of the dbForge SQL Tools bundle.

If you don't have dbForge SQL Complete installed yet, you can easily set it up.

(Also, as a reader of Database Star, I've got a coupon code for you. Use the code DATABASESTAR10SQL to receive a 10% discount for dbForge SQL Complete.)

If you already have it installed, you can skip this section and move onto the features.

You simply download it from the Devart website and follow the installation wizard.

The add-in is available for both SQL Server Management Studio and Visual Studio, and in this post I'll be demonstrating the features in SQL Server Management Studio.

Once you've installed SQL Complete, you'll see a new menu in the toolbar called SQL Complete:

Now you've got SQL Complete installed, let's check out the new features.

T-SQL Code Analyzer

The new T-SQL Code Analyzer is a great new feature in this version of SQL Complete.

It allows you to define a set of rules, and use these rules to analyze your SQL scripts, showing you if the script passes or fails these rules.

It's like a style guide checker or linting tool (if you've seen one in software development), but for SQL scripts.

I haven't used a feature like this before and I can see it being really useful. If you're the sole team member working on the database, you can define rules and ensure your scripts meet these rules. If you work with a team, the team can define the rules to follow, and everyone can use the same set of rules, saving time in development and ensuring a higher quality set of scripts.

Let's see how to analyze your code, and then how to view and edit the rules.

First, we'll start with a simple SELECT statement here.

To analyze the code, you can right-click in the SQL editor and select Analyze Code:

Or, you can go to the SQL Complete menu and select Analyze Code.

When you analyze the code, a new panel appears at the bottom of the screen.

In this example, there are two issues.

The first is that I've used SELECT *, which is generally a bad practice in SQL. It's better to define the individual columns.

The second is that I haven't specified the schema name. SSMS should pick this up based on the selection on the toolbar, but it's usually better to be specific and add the schema name before the table name.

So, that's how we run the analysis.

How can we see the full list of rules, and make changes?

We can go to the SQL Complete menu, then select Options.

We'll see a new window appear with all of the options available in the add-in.

Expand the Code Analysis entry and click on Profiles. You'll see a list of profiles that are used for the code analysis.

There is only one at the moment, and it's called Default.

At the bottom of the screen are a few buttons.

• Create New lets you create a new set of rules, which can be useful if you want a list for your team, or if you want to keep the default as a different profile.
• Add Existing lets you add an existing profile, which is handy if you have a profile for your team that can be shared.
• Open Selected will let you view the selected profile and the rules.
• Set as Active will let you indicate which profile will be the active one, if you have more than one.

We'll click on Open Selected.

Rules are contained within groups. You can expand the groups here to see all of the rules within it.

Each rule has an ID and a name. You can see the word Warning on the right, which can be changed to Error or Hint. There's also a description that is shown if you click on a rule, providing more context about the rule.

You can check or uncheck the checkbox on the left to include or exclude the rule from the profile.

You can save the profile or cancel any changes you've made.

Overall, this is a pretty powerful feature that should save you and your team a lot of time when working with SQL and improve your code quality.

Code Prompting Enhancements

The code prompting, or IntelliSense, is an existing feature in SSMS and is already pretty handy.

But SQL Complete makes this even handier, in several ways.

First, you now have the ability to autocomplete non-aggregated columns when using a GROUP BY keyword in your query.

Whenever you have a query with a mix of aggregate columns and non-aggregate columns, you need a GROUP BY keyword and then need to specify all of the non-aggregate columns.

This can be a bit of a hassle. You'll have to copy and paste from the SELECT clause, or type in the columns again, which can take a bit of time on larger queries.

With SQL Complete, you have the ability to automatically add this in.

Here's a part of a query to which we want to add a GROUP BY clause.

1SELECT dc.MaritalStatus, dc.TotalChildren, COUNT(*) Customers
2FROM DimCustomer dc

After the GROUP BY is added, you'll see the option in the auto-complete list to see all of the non-aggregate columns.

Select this, and the columns are populated.

We can run this and see the output.

This should be a real timesaver for you.

There are many other keywords and areas of SQL that are shown in the auto-complete in this version of SQL Complete:

• the PERSISTENT_LOG_BUFFER construct in CREATE DATABASE statements.
• RSA_OAEP algorithm in CREATE COLUMN ENCRYPTION KEY statements.
• a range of graph database functions
• more support for CREATE/ALTER AVAILABILITY GROUP
• the TERTIARY_WEIGHTS function

SQL Query History

There's an existing feature in SQL Complete called Query History. This allows you to view the history of queries that you have run on the local PC.

This can be helpful if you want to go back to previous queries, or see what you have run earlier in the day.

In version 7.0 of SQL Complete, there have been some updates to the UI of this Query History. 

First, the Date Range selection here has been updated. You can click on this date range here, and you'll see a drop-down of some options you may use often, from 5 minutes up to 30 days, and then different periods such as Last 7 Days or Current Year.

If you need something more specific, you can select Custom Range, and you'll see a window where you can select a custom start and end date.

When you select a date range, whether it's a custom range or one from the list, you'll see a list of queries that you have run within that date range.

You'll see a range of information about each query, such as when it was executed, the duration, the user, and more.

The next change is that a Clear button has been added. You can click this and the Query History is cleared.

Finally, if you change the layout of columns here, and then exit SSMS (or Visual Studio, if that's where you're using this add-in) and re-open it, the layout is preserved. This should save you a bit of time and remove a small annoyance from the tool.

Command Line Enhancements

The final set of updates within the latest version of SQL Complete version 7.0 is a few different command-line features.

First, we have Find Invalid Objects. This is a feature that lets you find invalid database objects.

You can run this script against a database, and it will find all objects that are invalid, such as stored procedures that refer to objects that don't exist.

Here's an example.

I can run the Find Invalid Objects script with this command:

1sqlcomplete.com /findinvalidobjects /connection:"Connection Timeout=30;Data Source=LAPTOP-D9BOFUUE;Encrypt=False;MultipleActiveResultSets=False;Integrated Security=True;Packet Size=4096;TrustServerCertificate=False" /database:gravity_books

When you run this on the command line, this is what you see:

This command is quite long, though.

But there's an easy way to generate this command.

To do this, go to SSMS (or Visual Studio), and go to SQL Complete, then Find Invalid Objects.

You can click on the Analyze button here to analyze your database.

Or, you can click on the Save Command Line button, and you'll get this popup where you can select a range of options for your command line command to run.

You can see the command here. You can also save it as a batch file, and then simply run this batch file to run the more complex Find Invalid Objects command.

Finally, there's also a similar wizard in the SQL Formatting feature.

To open this, go to the SQL Complete menu, and select SQL Formatter.

On the first screen of the popup, click on Save Command Line.

You'll see a similar command line screen as the Find Invalid Objects section.

Make any changes you want, save it as a batch file, and you can run the SQL Formatter command from the command line very easily.

Conclusion

That wraps up all of the new features in SQL Complete 7.0. If you work with SQL Server and haven't used this add-in, I recommend it.

It's a great add-in to improve your productivity when working with SQL Server, so you can spend less time on the manual parts of working with SQL and more time on valuable activities.

Check out dbForge SQL Complete here.

As you're a reader of Database Star, you can use the coupon code DATABASESTAR10SQL to receive a 10% discount for dbForge SQL Complete.

Thanks for reading!