Boost your productivity using free SQL tools

This article provides guidelines to make developers’ and DBAs’ working days a little bit easier with using free SQL tools.

ApexSQL Software has a wide suite of free tools for SQL Server, some of them are: ApexSQL Complete, ApexSQL Refactor, ApexSQL Search, ApexSQL Plan, ApexSQL Compare, ApexSQL Discover, ApexSQL Model, ApexSQL Propagate, ApexSQL Decrypt

Table of contents:

Free SQL tools for DBAs

Free SQL tools for Developers

Download free SQL tools

Free SQL tools for DBAs

ApexSQL Discover

This free SQL tool is built to automatically discover SQL Server instances, as well as SSRS, SSIS and SSAS services across the network. ApexSQL Discover allows scan process automation through custom scheduled jobs and provides email notifications with scan results. This tool provides detailed information on discovered servers with the real-time graph for CPU, memory and disk usage. For all registered instances, the notification about newly available updates are shown automatically:

Automatically discover SQL Server instances, as well as SSRS, SSIS and SSAS services across the network

ApexSQL Plan

ApexSQL Plan is the tool for identifying performance issues, deadlocks, problems with queries and query execution plans. ApexSQL Plan analyzes SQL Server queries, stored procedures and provides information for better understanding query performance characteristics:

Comparing SQL execution plan diagrams for the query optimization review

Additionally, with ApexSQL Plan, query execution data can be generated, reviewed, and collected from the Query store:

Query store data

Watch ApexSQL Plan introduction video for more information

ApexSQL Propagate

The main purpose of this free SQL tool is to execute multiple SQL scripts on multiple SQL Server databases at once. Before execution of the script, all scripts can be parsed to see if an execution of a script will pass or fail. All results of the SQL script execution can be saved (exported) as CSV files or as a plain text:

Adding databases to a list to process SQL script execution lists against

Free SQL tools for Developers

ApexSQL Compare

With ApexSQL Compare tool, files, folders, SQL Server objects can be compared with highlighting syntax difference. This tool has support for CLI, integration in SSMS and VS. Two-way script comparison and create a merged file can be performed. ApexSQL Compare supports Character, Line and Block comparison modes:

Initiating a SQL code compare with files with SQL code and different formats in SSMS

Watch ApexSQL Compare introduction video for more information

ApexSQL Complete

ApexSQL Complete is another free SQL add-in that integrates into SSMS and VS, which speeds up coding by automatically completing SQL code statements, filling in SQL code fragments, keeps track of all executed SQL queries, keeps track of all query tabs, creates CRUD procedures, searches through query results, exports query result, colors query tabs and more.

The code completion (auto-complete) feature is one of the main features of ApexSQL Complete for speeding up coding. By default, this option is enabled and can be found under the ApexSQL Complete menu:

Enable hint-list dialog

Once enabled, the hint list appears, offering a hint to complete keyword, SQL fragment, or even entire SQL statement each time when begin to type SQL code in the query editor:

The hint-list

Another useful feature is the Executed queries feature that keeps track of all executed queries. With this feature, the content of executed queries can be searched and reviewed. All executed queries can be re-used with double click on a desired query from the list:

List of executed queries

Create CRUD procedures

Often used operations when working with a database are CRUD (Create, Read, Update and Delete) operations. ApexSQL Complete has the Create CRUD procedures feature that automatically creates Select, Insert, Update and Delete stored procedures using customizable templates.

Right-click on a table and from the context menu choose the Create CRUD procedures command:

Create CRUD procedures command

Execution alerts

To prevent accidental data loss from executing a query. For example, a SQL query has a Delete statement without a Where clause, when executing such a query, the entire table will be wiped instead of only one row. To eliminate that possibility, use the Execution alerts feature.

When execute the SQL code, the Execution alerts feature analyzes the SQL code against a defined set of rules with the ability to block query execution and produce a warning message:

Execution alerts dialog

Snippets

One more feature that can speed up coding is the Snippets feature. This feature is useful when working with repetitive SQL code. Creating the T-SQL code snippets that insert predefined SQL fragments and replaces variables with context-specific values to generate SQL can save a lot of time:

Create/edit snippets

Tab coloring

When working with a lot of opened query tabs with a lot of different SQL Server instances, it becomes hard to see which query tab belongs to which SQL Server instance. The ApexSQL Complete Tab coloring feature provides custom connection color to a query tab which helps with continuous work on many different servers and databases simultaneously by giving them a better overview of which connection they are currently using:

Query tab coloring

Automatic tab renaming

One more ApexSQL Complete feature that can help in navigation between query tabs is Automatic tab renaming which analyzes the code inside the SQL query tab. This feature searches for Select, Update, Insert, Delete, Exec, Truncate or alter SQL statement in the SQL query tab and use these keywords, to set a new name for a SQL query tab:

Automatic tab renaming

Tab navigation

The Tab navigation feature saves opened/closed tabs, reviews and re-uses the closed tabs. This feature also has the ability to retrieve unsaved SQL query scripts after SSMS/VS crashes, a lot of work can be saved from losses with this feature:

Save opened/closed tabs

Go to object

To quickly find an object in Object Explorer from a particular database, the Go to object feature can be very handy. In Object Explorer, select a database, from ApexSQL Complete menu, choose the Go to object command, the Go to object window will appear. In the Search box, enter the name of the object that you want to find. In the Go to object grid, the search results for that phrase will be listed:

Find SQL objects using free ApexSQL Complete add-in

Double-click on an object from the list of results, and that object will be selected in Object Explorer:

Find SQL object using Go to object feature from free ApexSQL Complete add-in

Navigate to object

The Navigate to object feature finds database objects in Object Explorer from the query tabs. Right-click on the name objects in a query tab and, from the right-click context menu, choose the Navigate to object command. This will find and select the database object in Object Explorer:

Finds database objects in Object Explorer from the query tabs

Results search

Another search feature that comes with free SQL add-in is the Results search feature. This feature searches through the results grid to find text that is set in the Search term box. All found data in the results grid will be highlighted:

Search data in results grid

Export to Excel

The Export to Excel feature exports data from the results grid to Excel in just a few clicks:

Export data from results grid to Excel using free ApexSQL Export to Excel feature

ApexSQL Decrypt

ApexSQL Decrypt tool can decrypt SQL procedures, functions, triggers and views with a single click on multiple databases and SQL Server instances:

View all objects by SQL object decryption status

Watch ApexSQL Decrypt introduction video for more information

ApexSQL Model

Main purpose of this free SQL tool is forward and reverse engineering, such as creating a database from a model, creating a script of objects on diagram, updating the database from the model and creating the model from the database. Database model diagram can be created and changes can be synchronized with existing databases. Model diagrams can be printed, exported as snapshot or into BMP, JPEG and PNG formats:

Displaying a data model with a free SQL Server data modeling tool

ApexSQL Refactor

Powerful free SQL add-in for refactoring and formatting SQL code, with over 200 formatting options and 15 refactoring features.

Formatting

One of the main purposes of ApexSQL Refactor is to beautify SQL code using various formatting options under the Options window:

Format SQL code using free SQL tool

Format database objects in batch

The Format SQL objects feature can format multiple SQL objects using a specified SQL profile simultaneously.

In Object Explorer, select a database where objects are located that you want to be formatted (stored procedures, functions, views, triggers). Right-click, and from the context menu, choose the Format SQL objects command:

Format multiple database objects at once

From the Profile drop-down, choose the preferred formatting profile and then, from the list, choose objects that want to be formatted and click the Create script button. This will open a new query tab with formatted SQL code filled in:

Formatted SQL script

Click the Execute (F5) button to apply formatting changes.

Format T-SQL scripts in a batch

To format multiple T-SQL scripts at once, use the Format SQL scripts feature. From the ApexSQL Refactor main menu, under the Other formatting sub-menu, choose the Format SQL scripts command:

Format multiple T-SQL scripts at once by using free SQL tool

This will open the Format SQL objects window, from the list, select the T-SQL scripts that want to be formatted and from the Profile drop-down box, choose desired formatting profile and click the OK button:

Format SQL objects window

Online SQL formatter

Beside desktop formatting version, ApexSQL has a free SQL tool for web formatting SQL code which is called Online SQL formatter, and it has the same SQL formatting options as the desktop version:

Free SQL tool for web formatting

CLI formatting

With ApexSQL Refactor the SQL scripts can be formatted using one of four build-in formatting profiles or use your own customized formatting profile using CLI:

CLI SQL formatting

Refactoring

Beside the Safe rename feature that is already mentioned in the ApexSQL Search section of the article, ApexSQL Refactor has Split table, Add surrogate key, Change parameters and Replace one-to-many relationship refactoring features, which are located under the Other refactors sub-menu:

Refactoring your code using free ApexSQL features

Convert to code

The Convert to code feature converts a SQL script into a language-specific syntax (Java, C#, Perl, PHP):

Converts a SQL script into a language-specific syntax

Encapsulate code as

The Encapsulate code as feature converts (refactors) any valid T-SQL code from a query tab to a stored procedure, view, scalar/table inline function:

Converts (refactors) any valid T-SQL code

ApexSQL Search

Often, developers and DBAs need to search databases to find specific objects or data. For example, if a database function or procedure contains a specific table column or a variable name, or a table that contains specific data needs to be searched for, SQL Server Management Studio (SSMS), nor Visual Studio (VS) does not provide an easy way (solution) to achieve these.

ApexSQL Search is a free SQL add-in that integrates into SSMS and VS. With this add-in, you can search through a database in finding desired database objects (User tables, Columns, Indexes, DDL triggers, Parameters, Constraints, etc.), data in the SQL Server tables and views. Beside finding objects, data in the databases, ApexSQL Search provides the feature for adding new or editing existent property, as well a feature for safe renaming database object without breaking any dependencies.

Text search

To launch Text search feature to search data in the tables and view, start SSMS/VS, connect to desired SQL Server instance and from ApexSQL Search main menu, choose the Text search command:

ApexSQL Search menu

In the Search text box, enter the desired string (data) that needs to be searched. Under the Database combo box, select a database where you want to do the search and press the Find button:

Find SQL data by using Text search feature from  free ApexSQL Search add-in

Object search

To find a looked-for object in a database, use the Object search feature. From ApexSQL Search menu, choose the Object search command:

ApexSQL Search menu

Type the searched string in the Search text box, from the Database combo box, choose the databases where you want to find the wanted string and click the Find button:

Find SQL object by using the Object search feature from free ApexSQL Search add-in

To find an object from the Object search grid in Object Explorer, in the Object search grid, double-click on the chosen object and that object will be highlighted in Object Explorer:

Find objects in Object Explorer by using the Object search feature

Or the Navigate to object explorer node from the Object search grid context menu can be used.

Furthermore, all results from the grid can be saved as CSV, HTML or XML file.

Edit executed properties

To easily display descriptions of an object in the database, edit them or add a new one, use the Edit extended properties feature.

In Object Explorer, select a database, right-click and from the context menu, choose the Edit extended properties command. The same option is available through the ApexSQL Search menu:

Edit extended properties command

Under the Extended property editor, find the object which description needs to be edited, click on the ellipse (…) button under the Description column, in the Edit extended property window, enter the description and press the Save button:

Edit/add table/column description

Safe rename

One more useful feature that comes with the installation of ApexSQL Search is the Safe rename feature. This feature can rename SQL Server tables, table columns, stored procedure, functions, parameters without breaking any dependencies:

Safe rename SQL Server tables, table columns, stored procedure, functions, parameters without breaking any dependencies

ApexSQL Trigger Viewer

ApexSQL Trigger Viewer is the free SQL tool (reporting) designed for end-users who only need to view auditing reports.

ApexSQL Snapshot Utility

Free SQL utility which can create a lightweight snapshot of a database schema. This tool can be used as a data source in ApexSQL Build, ApexSQL Diff and ApexSQL Doc.

Download free SQL tools

All mentioned free SQL tools can be downloaded from the ApexSQL download page

 

September 3, 2019