Boost your productivity using ApexSQL Fundamentals Toolkit for SQL Server

This article provides guidelines to make developers’ and DBAs’ working days a little bit easier with using ApexSQL Fundamentals Toolkit for SQL Server packet.

Table of contents:

ApexSQL Analyze

This is the graphical SQL dependency viewer. All objects from the SQL Server database are present with different shapes and colors to improve their visual look and readability. It determines object interrelationships within the database and allows customization of the resulting SQL dependency diagram appearance:

Showing shapes and color layout style in a SQL join graphical visualization

ApexSQL Complete

ApexSQL Complete is a 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 upcoding. 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 which 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 a 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 executing 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 the 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 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 Propagate

The main purpose of this 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 plain text:

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

ApexSQL Refactor

Powerful 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 the desired formatting profile and click the OK button:

Format SQL objects window

CLI formatting

With ApexSQL Refactor the SQL scripts can be formatted using one of four build-in formatting profiles or use your 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 tableAdd surrogate keyChange 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 Script

ApexSQL Script is a SQL Server development tool that can script databases into scripts, script folders, deployment packages and commit directly to source control projects. ApexSQL Script also supports scripting from SQL Azure Databases and Amazon RDS for SQL Server:

Selecting various output options for a SQL script

Watch the ApexSQL Script introduction video for more information.

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 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. Besides finding objects, data in the databases, ApexSQL Search provides the feature for adding new or editing existing property, as well a feature for safe renaming database objects without breaking any dependencies.

Text search

To launch the Text search feature to search data in the tables and view, start SSMS/VS, connect to the 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 the 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 files.

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

 

November 19, 2020