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:
- ApexSQL Compare
- ApexSQL Complete
- ApexSQL Decrypt
- ApexSQL Model
- ApexSQL Refactor
- ApexSQL Search
- ApexSQL Trigger Viewer
- ApexSQL Snapshot Utility
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:
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:
Additionally, with ApexSQL Plan, query execution data can be generated, reviewed, and collected from the Query store:
Watch ApexSQL Plan introduction video for more information
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:
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:
Watch ApexSQL Compare introduction video for more information
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
Double-click on an object from the list of results, and that object will be selected in Object Explorer:
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:
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:
Export to Excel
The Export to Excel feature exports data from the results grid to Excel in just a few clicks:
ApexSQL Decrypt tool can decrypt SQL procedures, functions, triggers and views with a single click on multiple databases and SQL Server instances:
Watch ApexSQL Decrypt introduction video for more information
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:
Powerful free SQL add-in for refactoring and formatting SQL code, with over 200 formatting options and 15 refactoring features.
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:
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:
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:
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:
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:
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:
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:
Convert to code
The Convert to code feature converts a SQL script into a language-specific syntax (Java, C#, Perl, PHP):
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:
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.
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:
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:
To find a looked-for object in a database, use the Object search feature. From ApexSQL Search menu, choose the Object search command:
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:
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:
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:
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:
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:
ApexSQL Trigger Viewer is the free SQL tool (reporting) designed for end-users who only need to view auditing reports.
All mentioned free SQL tools can be downloaded from the ApexSQL download page
September 3, 2019