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
- ApexSQL Complete
- ApexSQL Decrypt
- ApexSQL Propagate
- ApexSQL Refactor
- ApexSQL Script
- ApexSQL Search
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:
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:
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 Query history 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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
Export to Excel
The Export to Excel feature exports data from the results grid to Excel in just a few clicks:
ApexSQL Decrypt
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
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:
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 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 the desired formatting profile and click the OK button:
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:
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:
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:
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:
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:
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:
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:
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 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:
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:
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:
November 19, 2020
