Command Line Wizard in dbForge SQL Complete

Extensive automation of recurring operations with databases is the cornerstone of all major dbForge tools. After all, who would mind replacing daily manual setup of a wizard with a single reusable command that can be launched from the Command Prompt within a few seconds?

When it comes to Microsoft SQL Server, the one command-line utility that comes to mind is sqlcmd, which is installed alongside SQL Server and used to execute T-SQL statements, scripts, and stored procedures against databases from the command line. However, simple and effective as it is, still it's not used as much as SQL Server Management Studio. And if your work with databases covers SQL Server, you're quite likely to be a user of SSMS as well.

If you are, you ought to know there's a way to bring command-line automation to SSMS just as effectively, and it comes with dbForge SQL Complete, a multifunctional add-in for SSMS that mainly focuses on completion-aided SQL coding, formatting, refactoring, and debugging. SQL Complete is one of those tools that aims to make your routine work with databases far less tiresome and time-consuming, and it succeeds with the help of features like the Command Line Wizard. The basic principle of the Wizard is simple: you get a command ready once, then you reuse it whenever you need.

If you're interested, read on—you'll learn all about the Wizard and see how it can help you automate a few vital operations like SQL code formatting and search for invalid objects within your SQL Server databases.

Overview of the Command Line Wizard

Let's get started with a brief overview. The Command Line Wizard converts the options of a feature (e.g., code formatting) into the command-line syntax, which can further be saved to a BAT file. Afterwards, you can execute this file at any given moment—or schedule it for regular execution using tools like Windows Task Scheduler.

The Wizard is typically accessed by clicking a button called Save Command Line, located in the interface of the required feature (you will see what it looks like in the examples below).

Benefits of the Command Line Wizard

But before we proceed to the said examples, we'd love to outline the key benefits of the Command Line Wizard that make it superior to similar solutions.

• Convenience: The Command Line Wizard is always at hand, seamlessly integrated into each supported feature and accessed with a single click.
• Auto-generation of command-line scripts: Once you configure the options of a feature, you only need to click Save Command Line—the Wizard will open, and the required script will already be there. If necessary, you will be able to tweak it to your further preferences and instantly validate it. Quite obviously, it's a far better and faster option than writing a script from scratch.
• Reusable BAT files: As we said, your command-line scripts can be saved to BAT files for further use directly from the Wizard.

Now let's see how it works!

Finding invalid objects with the Command Line Wizard

Okay, here comes the first example. The feature's called Find Invalid Objects, and it pretty much speaks for itself—it helps you learn whether any of your database objects have a reference to other objects that no longer exist or have been modified. It's a vital feature that helps maintain database integrity, and what's more, it implies recurring use. That's a perfect candidate for our case.

1. In SSMS, go to SQL Complete > Find Invalid Objects.

2. Select the database(s) to be checked. If necessary, you can pick another connection right here. Once you're ready, click Save Command Line on the toolbar.

3. The Command Line Wizard opens instantly, and you can see that the script has been auto-generated for the selected database.

4. Optionally, you can click Validate to make sure that your script is going to work properly.

5. Finally, you can click Save and pick a name and destination for your BAT file. Click Save again, and the file will be saved.

As you can see, the file has been saved. You're free to launch it at any moment.

6. Once you launch the file, the search for invalid objects will be performed in a matter of moments.

Formatting SQL code with the Command Line Wizard

How about another case? Let's see how to set up automated code formatting of SQL files. We'll take the following SQLQuery.sql file as an example.

1. In SSMS, go to SQL Complete > SQL Formatter.

2. The SQL Formatter Wizard opens. Select the preferred option (in our case, it will be Files) and click Next.

3. Click Add and select the SQL file(s) you want to format. Then click Save Command Line in the lower left corner of the Wizard.

4. You will see an interface not unlike that of our previous case. Similarly, here you can tweak your script and validate it. Once you're ready, click Save.

5. Specify the file name and the destination folder for your BAT file and click Save again.

The file has been saved successfully.

6. Launch your BAT file and wait a few moments until it's executed.

7. Now, open your SQL file. It will be successfully formatted according to the active formatting profile.

You can set an active formatting profile in SQL Complete > Options > Formatting > Profiles. As you can see, Default is currently set as the active profile. You can pick another profile or even create one that will match any specific requirements.

That's it! Try it out and you'll see that setting up and running these operations from the command line will take even less time than reading this guide.

Why use the Command Line Wizard?

Simply because it's faster and more convenient. It's always by your side, it does the job with minimum effort on your behalf, and it helps avoid any errors that might occur when writing scripts manually.

Conclusion

Command-line automation is an integral part of the entire dbForge product line. Across various products, you can automate a huge number of operations. You can set up automated import and export of your data, precisely tailored to your requirements. You can effortlessly automate the generation of stuff like test data, database documentation, and data reports. You can compare and synchronize databases from the same command line, and more.

And when it comes to SQL Complete, you can easily locate invalid objects and perform formatting in a matter of moments. Want to see it in action? Get SQL Complete for a free 14-day trial and explore all of its capabilities that will undoubtedly level up your SQL coding from day one.