SQL SERVER – Free Database Search and Dependency Analysis

This article covers the main features of ApexSQL Search, a free SQL Server Management Studio and Visual Studio add-in for SQL Server database object and text searching and for dependency analysis.

You can download the most recent version of ApexSQL Search from this link and play along through the article.

Database object search

ApexSQL Search will show all results based on the specified search, no matter if it is a part of the object name, or object body. Even system objects can be searched. To initiate the object search, right click the database or any object from the Object Explorer pane and from the ApexSQL Search menu, select the Database object search option:

1 SQL SERVER   Free Database Search and Dependency Analysis

The same option is available from the ApexSQL Search main menu, assuming that the database that will be search is selected in the Object Explorer:

2 SQL SERVER   Free Database Search and Dependency Analysis

This will initiate the Database object search tab:

For the specified search, the user can set the search options, so the results will include all matches from the object name, object body, system objects, or by searching for an exact match:

3 SQL SERVER   Free Database Search and Dependency Analysis

Using the Object type filter, searching can be narrowed down to the specific object types (e.g. tables):

4 SQL SERVER   Free Database Search and Dependency Analysis

For each of the search result, the user can perform variety of commands, using the right-click context menu:

5 SQL SERVER   Free Database Search and Dependency Analysis

ApexSQL Search can navigate to the specific object from the results grid to the Object Explorer using the Navigate to object explorer node option.

The content of each cell from the results grid can be copied to clipboard using the Copy cell option.

Complete list of search results can be copied to clipboard or saved in one of the available formats:

6 SQL SERVER   Free Database Search and Dependency Analysis

7 SQL SERVER   Free Database Search and Dependency Analysis

Dependency Viewer

In addition to manipulating with the search results, within just a single click the user will be able to review all dependencies of the selected object, by using the View dependencies option. The option is available on each individual object, on a database level:

8 SQL SERVER   Free Database Search and Dependency Analysis

The same option is available from the ApexSQL Search main menu (assuming that the corresponding database or object is selected in the Object Explorer pane):

9 SQL SERVER   Free Database Search and Dependency Analysis

This will initiate the Dependency viewer form:

10 SQL SERVER   Free Database Search and Dependency Analysis

Besides the main grid where the graphical representation of object dependencies is shown, there are four main ‘panes’ to manage the dependency appearance and accuracy:

  • Object filter (upper left) – narrowing down the scope of objects type to be shown in the diagram
  • Object browser (lower left) – filtering through the individual object list, grouped by the object type. In addition to this, using the filter row, the user can easily find an object, or schema
    11 SQL SERVER   Free Database Search and Dependency Analysis
  • Dependencies – (lower right) – for each selected object in the main grid (i.e. for the ufnGetProductDealerPrice SQL function) all dependent objects will be listed
  • Overview – (upper right) – gives a general overview of the whole diagram and the current zoomed part shown in the main grid

Database text search

Similar as the Database object search option, an option for text search is available from the Object Explorer pane, by right-clicking the database, or any of the object, or in the ApexSQL Search main menu:

12 SQL SERVER   Free Database Search and Dependency Analysis

This will initiate the Database text search tab:

13 SQL SERVER   Free Database Search and Dependency Analysis

There are variety of options to set before the search process is started. Besides this, the user can narrow down the scope of the search by selecting specific tables from the Select objects to search pane to the left. The user can search through system objects, include empty fields as well as null fields.

From the search results the same context menu is available as explained in the database object search part.

The only difference is that ApexSQL Search offers additional details for each of the search results in the database text search results grid, by clicking the ellipsis button for the specific search result:

14 SQL SERVER   Free Database Search and Dependency Analysis

This will give an overview of the exact row in a table where the selected result is found.

Safe rename

This option allows the user to rename tables, views, stored procedures, functions, columns and parameters without breaking any dependencies. Using the Safe rename option, an object can be moved from one schema to another:

15 SQL SERVER   Free Database Search and Dependency Analysis

ApexSQL Search will create a script that will be used in the renaming process. The script can be reviewed and changed additionally in the query window. All actions that will be executed prior to the renaming will be shown in the Sequence tab of the Safe rename form.

Extended property editor

The Extended property editor feature allows you to review the existing and to add a new extended property. To initiate the Extended property editor, select the Edit extended properties option, either from the Object Explorer right click menu or from the ApexSQL Search main menu:

16 SQL SERVER   Free Database Search and Dependency Analysis

To access the extended property of the specific object, click the ellipsis button, and review or modify the extended property. Once edited, the extended property will be saved directly in the database:

17 SQL SERVER   Free Database Search and Dependency Analysis

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Database source control with ApexSQL software

This article covers the main features of ApexSQL tools that are involved with SQL source control. For the purpose of the article, we will use ApexSQL Source Control, ApexSQL Diff and ApexSQL Build.

The latest version for any of the mentioned tools can be downloaded from ApexSQL download section, and you can play along while reading the article.

Apex SQL Source Control

ApexSQL Source Control is a SQL Server Management Studio add-in that allows version controlling SQL database objects. It natively supports all major source control systems such as Git, Mercurial, Perforce, Subversion and Team Foundation Server (including Visual Studio Online) requiring no additional client tools to be installed

Source control development “models”

ApexSQL Source Control supports two database development models: Shared and Dedicated.

Dedicated model means that each developer is working on a local instance of a database. It allows developers to work independently and test changes without affecting the rest of the team. However, it requires a separate license of SQL Server for each developer. Developers have to be aware of conflicts that may occur in case more than one developer is working on the same object and want to commit it to the repository. We will explain conflicts later in this article.

Shared model means that all developers are working on the same database and that all changes are applied directly against a database, and after that committed to the repository. Conflicts cannot occur in shared model. However, a developer can override other’s change, so it is essential that the entire team is aware of all changes and all objects that are currently in use by someone else.

Initial database linking

To link a database, right-click on it in the Object Explorer pane and from the ApexSQL Source Control menu click the Link database to source control option:

image001 SQL SERVER   Database source control with ApexSQL software

Choose the source control system and database development model:

image002 SQL SERVER   Database source control with ApexSQL software

In case the shared model is selected, an additional option allows you to choose a host database for the framework objects that ApexSQL Source Control is using.

Select objects to be included in version control. By default all supported objects are included (Learn more):

image003 SQL SERVER   Database source control with ApexSQL software

Specify credentials for the repository, along with the repository path and particular project inside the repository if needed (Learn more):

image004 SQL SERVER   Database source control with ApexSQL software

After the linking is done, all objects included in the linking process will appear in the Action center tab. This is the main window used by ApexSQL Source Control in communication with the repository.

It shows the comparison results for each object, comparing its current state in the database with the latest version on the repository.

image005 SQL SERVER   Database source control with ApexSQL software

After the initial commit is performed, the Action center tab will show that the local database and the repository are synced. Each change made after the initial commit will appear upon refreshing the Action center tab, no matter which user made a change.

Check out an object

To check out an object, right click on it from the Object Explorer, and choose the Check out option from the ApexSQL Source Control menu:

image006 SQL SERVER   Database source control with ApexSQL software

Check out serves for information purposes, meaning that it can be overridden by other developer.

Lock an object

To prevent other developers from working on an object, it is necessary to use the Checkout and lock option. It is located in the ApexSQL Source Control menu:

image007 SQL SERVER   Database source control with ApexSQL software

By locking an object, the rest of the team will be prevented from making any changes, until an object is unlocked, or after the change made against an object is committed to the repository.

Each object status is followed by the appropriate icon in the Object Explorer pane, so the entire team is aware of objects that are checked out, locked, or edited:

image008 SQL SERVER   Database source control with ApexSQL software

Besides following the object status in the Object Explorer pane, the same information is available through the Object status form, where all object are listed along with the information about the status of an object and the user who performed an action:

image009 SQL SERVER   Database source control with ApexSQL software

Resolving conflicts in dedicated development model

Conflicts occur within the dedicated database development model in cases when developer A modified an object and committed changes to the repository while developer B was working on the same object version prior to the change. In this situation, when developer B tries to commit the change, a conflict occurs, since the version that developer B used was not the latest one from the repository. ApexSQL Source Control does not allow any action (push to the repository or pull to a database) until the conflict is resolved. For resolution, all changes from the database can be committed to the repository (keep local), the latest changes from the repository can be applied to a database (take remote), or each conflict can be resolved manually by using either the built-in line by line merging tool or one of the 3rd party merging tool (Learn more):

image010 SQL SERVER   Database source control with ApexSQL software

Using Labels/Tags

A Label or Tag represents the current state of the repository (snapshot of the repository or the check point) and it is often used when a stage of database development is finished/milestone is achieved, or a testing is finished. In this case, a label is created, so the database can be easily reverted to the previous state if needed.

To create a label using ApexSQL Source Control, right click the database in the Object Explorer and from the ApexSQL Source Control menu click the Create label option. For reviewing and applying specific labels, use the Show labels option from the same menu:

image011 SQL SERVER   Database source control with ApexSQL software

Applying a label with ApexSQL Source Control will initiate the dependency aware mechanism that will take the created label and create a script that will be applied against the database. The entire process is wrapped as a transaction which means that either all is applied or nothing. Before applying a label, a developer can explore the script, modify it or save it for later usage:

image012 SQL SERVER   Database source control with ApexSQL software

History

Through the history form, a developer will be able to explore each commit (change set) and to compare each version of an object committed to the repository with the current version from the database or any other version as well. Even more, the selected version can be retrieved from the repository and applied to the database with a single click (Learn more):

image013 SQL SERVER   Database source control with ApexSQL software

Compare/sync the repository with database

Using ApexSQL Diff, a SQL Server database schema comparison, and synchronization tool, a database can be compared with the repository.

ApexSQL Diff will compare specified sources, by comparing the latest version from the repository with the database, or it can be set to compare the database with the specific label.

image014 SQL SERVER   Database source control with ApexSQL software

Source control from the Destination section can be set in a way that the latest version on the repository is used for comparison, or any label specified:

Source control, as the destination, will by default be the latest version in the repository, unless a label is specified

image015 SQL SERVER   Database source control with ApexSQL software

Build a database from the repository

ApexSQL Build can be used to take the latest changes from the repository (or a specific label) and create a deployment and/or immediately create/update the target database.

To build a database directly from the source control repository, select the Build new database option:

image016 SQL SERVER   Database source control with ApexSQL software

Specify the mechanism of deployment:

image017 SQL SERVER   Database source control with ApexSQL software

Specify the repository that holds the database to be deployed:

image018 SQL SERVER   Database source control with ApexSQL software

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Free SQL Complete Add-in For SSMS

This article covers main features of ApexSQL Complete, free SQL complete add-in. You can download the most recent version, and check its features while reading the article.

ApexSQL Complete is a free Visual Studio and SQL Server Management Studio add-in that will auto-complete SQL statements and speed up coding with built-in snippet library, help managing opened and closed query windows, track all executed queries and more. In this article, we will explain how to use some of the core features.

Auto-complete using the hint list

Auto complete feature provides intelligent code completion through the hint list, allowing you to find an appropriate hint and to fill in SQL keywords, as well as database schema, object, parameter, and variable names. As an example, if you start typing “CRE”, the hint list appears offering the CREATE keyword:

addin1 SQL SERVER   Free SQL Complete Add in For SSMS

The hint list offers context sensitive suggestions based on the current cursor position in the query. This means that if you type the USE keyword, the hint list offers database list:

addin2 SQL SERVER   Free SQL Complete Add in For SSMS

In addition, you can drill down to the column level and choose the specific column directly from the hint list:

addin3 SQL SERVER   Free SQL Complete Add in For SSMS

If you need additional information about any object from the hint list, move the mouse pointer over the object and hold it for a second, the Object script window appears with complete script of the object including extended properties shown in the Description tab:

addin4 SQL SERVER   Free SQL Complete Add in For SSMS

To view the script for encrypted objects, select the Decrypt encrypted objects option in the add-in options:

addin5 SQL SERVER   Free SQL Complete Add in For SSMS

The Auto complete feature will not just help you to find the appropriate hint, but will also complete entire fragment of SQL code as INSERT statement, or JOIN statement:

addin6 SQL SERVER   Free SQL Complete Add in For SSMS

To auto-complete INSERT statement, check the Insert full INSERT statement option in the add-in options:

addin7 SQL SERVER   Free SQL Complete Add in For SSMS

Full INSERT STATEMENT will be auto-completed as follows:

addin8 SQL SERVER   Free SQL Complete Add in For SSMS

Snippets library and using snippets

The Snippets feature allows you to insert custom-made T-SQL code snippets, and often used SQL statements into SQL scripts. SQL coding with snippets can be achieved in two ways: using snippets from the built-in library (which are also editable) or creating new snippets.

New snippets can be created from the Snippet tab in the add-in options, or directly from the query window.

Snippet library

Built in T-SQL snippet library contains over 200 snippets:

addin9 SQL SERVER   Free SQL Complete Add in For SSMS

In order to use any of the predefined snippets, just pick it from the hint list, and it will be automatically deployed to the query.

Create a new SQL snippet

Clicking the Add command in the above image allows you to create a new snippet:

addin10 SQL SERVER   Free SQL Complete Add in For SSMS

Another way to create a snippet is to select the New snippet command from the query window context menu:

addin11 SQL SERVER   Free SQL Complete Add in For SSMS

This will open the same form as when you are creating a new snippet from the Snippets tab under the add-in option, but will automatically add SQL code from the query editor to the Code section:

addin12 SQL SERVER   Free SQL Complete Add in For SSMS

To create a new snippet from a part of SQL code in the query window, select the specific part of code and initiate the New snippet command from the right-click context menu.

Tab navigation

The Tab navigation feature allows you keep the track of all opened and recently closed tabs, from the moment of enabling the feature. It also can help in saving the current tab session, recovering tabs from the session prior to unexpected host application closing. To enable the Tab navigation feature, go to the add-in options, and enable it under the Tab navigation tab:

addin13 SQL SERVER   Free SQL Complete Add in For SSMS

In the same tab, you can enable restoring workspace on startup, or restoring session prior to unexpected host application closing. Once this is enabled, all opened and closed tabs will be kept so you can always navigate to any of them and continue working. The Tab navigation pane will be shown below the query window:

addin14 SQL SERVER   Free SQL Complete Add in For SSMS

For each of the logged query you will have details such as tab caption, creation date and time, and you can instantly preview the content of the query in the right side of the Tab navigation form. In the top of the form, you have a Search field, so you can easily find specific content of the query.

In the add-in options, you can set the timeframe for keeping the closed tabs in the Tab Navigation form. All queries older than a setting of the Delete tabs older than option, under the Tab navigation option, will be automatically removed.

Executed queries

The Executed queries feature keep all executed queries saved, so you can easily browse through the list of executed queries and get back to any of them, at any time.

To enable the Executed queries, check the Log executed queries option in the add-in options, under the Executed queries tab:

addin15 SQL SERVER   Free SQL Complete Add in For SSMS

All queries executed from the moment of enabling the feature will be logged, and you can access them from the Executed queries form, which you can initiate from the add-in menu:

addin16 SQL SERVER   Free SQL Complete Add in For SSMS

In the Executed queries form, you can find details about each query executed including date of execution, status (did it failed or run successfully), which user executed particular query, related database and duration of the execution.

You can search through the content of queries for a specific information, and filter the list so it shows only queries executed in the specified time range:

addin17 SQL SERVER   Free SQL Complete Add in For SSMS

Double-clicking any of the queries from the list will open it in the query editor, so you can continue working if there is a need.

Copy result as

The Copy result as feature allows you to copy query execution results, and save it in one of the following formats: CSV, HTML, or XML. As an example, a result of a simple SELECT statement can be copied by right clicking directly in the results grid:

addin18 SQL SERVER   Free SQL Complete Add in For SSMS

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Security Auditing With ApexSQL Audit

SQL Servers and databases are probably the most critical IT parts of an enterprise. The most important and confidential data are stored there and even a potential issue with that data can be business critical. In situations where significant preventive/protective actions are applied, such measures will not be of any help in determining the cause of an incident when it occurs. For that purpose, establishing the SQL Server continuous auditing and especially being informed, timely of security configuration changes have been of the utmost importance.

SQL Server security auditing with ApexSQL Audit ensures the early or immediate detection of any SQL Server security changes allowing the DBAs to prevent it to cause an unwanted security breach or even worse potential data loss, interruption or hindered server operations. Being informed, timely about any security issue or even about the potential issue is not important only when immediate actions are required, but also for continuous auditing purposes.

In order to fulfill not only basic auditing requirements and to ensure the granular and precise auditing, ApexSQL Audit is featured with two types of auditing filters: Simple and Advanced.

Even though it is named “Simple” for its ease of use, this is quite a comprehensive filter which allows a high level of granularity in filtering. In situation when SQL Server security auditing is required, the following security related events can be specified for auditing at the server and database level.

Simple filter

Server level security related events:

 apexaudit 01 SQL SERVER   Security Auditing With ApexSQL Audit

Additionally, there is an easy option to include or exclude specific login from auditing using the Logins filter when such requirement exists (excluding the trusted users for example)

apexaudit 02 SQL SERVER   Security Auditing With ApexSQL Audit

Database level security related events:

 apexaudit 03 SQL SERVER   Security Auditing With ApexSQL Audit

ApexSQL Audit’s simple filter allows specifying SQL Server security auditing requirement for each security operation independently, but what’s more ApexSQL Audit allows specifying filtering conditions for all individual SQL Server auditing operation on both, server and database levels.

With a few simple mouse clicks, the desired SQL Server security auditing can be set according to specific security requirements, and Simple filter is the fastest way for configuring auditing of any important auditing requirements.

Advanced filter

Even though it is highly granular, the simple filter still doesn’t allows meeting some fine tuning requirements imposed by some advanced users or specific auditing requirements, so ApexSQL Audit also has a highly granular filter that can meet even the most demanding SQL Server auditing/compliance requirements and auditing precision. The Advanced filter utilizes a logical set of operators that can be selected for defining the auditing filter condition.

The image below displays all data fields available in the advanced auditing filter:

apexaudit 04 SQL SERVER   Security Auditing With ApexSQL Audit

Depending on the data field selected is, is not, contain and does not contain operators will be available for filtering. By using the advanced filter, user can create the auditing rule that will ensure any level of auditing precision required

apexaudit 05 SQL SERVER   Security Auditing With ApexSQL Audit

The advance filter is actually interpreted as the standard logical expression in the background where indentation represents the bracket and grouped conditions are treated as enclosed between the brackets. There is no limitation in the number or depth of filtering rules used.

The Text data filter will additionally allow meeting some very specific requirements as it is capable to parse the actual T-SQL of for audited event and then to collect or filter out the audited event depending on the used condition

apexaudit 06 SQL SERVER   Security Auditing With ApexSQL Audit

In the example above, the filtering condition will audit and log only the alter login and create login events which doesn’t fulfill the company rule that Enforce password policy and Enforce password expiration must be enabled for these events, so DBA can be timely notified and take necessary steps to fix that.

As already stated, it is very important and usually required to be informed timely about any SQL Server security auditing issue or event. In order to meet such requirement, ApexSQL Audit provides the capable alerting system that consists of:

  • Built-in system alerts
  • User defined data alerts for alerting on captured events
  • Custom script alerts for alerting on any value that can be retrieved via SQL script

ApexSQL Audit utilizes the true real time alerting since alerting engine will intercept and process the audited event, according to defined alerting condition even before the information is stored in the central repository database, allowing user instant notification when alert is raised.

Some highlights of the ApexSQL Audit alerting system:

  • Fully customizable alert name and notification options (user definable text of the alert name, subject and body text)
  • Alerting engine utilize the same advanced filter engine used for the auditing filter which allows very precise alerting conditions to be created
  • Allows alert email notification with ability to define individual recipient email address for each alerting condition created
  • Unlimited number of created alerts
  • Editing of built-in system alerts to adjust them according to individual needs
  • Editing, removing, disabling or deleting of user created alert
  • Alert history viewer with unlimited number of stored alerts
  • Detail information in alert body about the event

 apexaudit 07 SQL SERVER   Security Auditing With ApexSQL Audit

All collected and stored SQL Server security auditing events should be presented in a human readable form for any internal purpose or on request of an auditor. Besides being able to ensure reporting ability within the standard GUI, ApexSQL Audit features the Web based reporting ability. Web reports allows full reporting ability from any computer within the local network using the standard web browser. Both, the GUI, and Web-based reports are equally capable and equipped. Regardless of whether user decides to use the GUI or web reports, there are two types of reporting –common reports and custom reports.

Common reports consist of 11 predefined basic reports including the comprehensive filtering abilities, which ensures the quick and easy preview of collected events but also the precise reporting on required audited events

apexaudit 08 SQL SERVER   Security Auditing With ApexSQL Audit

The security related reports are Security configuration history, Logon activity history, Permission changes and Unauthorized access

 apexaudit 09 SQL SERVER   Security Auditing With ApexSQL Audit

For those who wish more from reporting, ApexSQL Audit has built-in custom reports feature that grants the full freedom in creating and organizing of reporting on collected during the SQL Server auditing. Again, the advanced filter form is used here as well (it is consistent through all parts of application) making any customization in ApexSQL Audit custom reports or any other part of application to be easy and consistent. Custom reports allow creating the precise filtering condition that can be saved and reused whenever it is needed

apexaudit 10 SQL SERVER   Security Auditing With ApexSQL Audit

There is no limitation in how many reports user can create and save, which allows creating the general overview, thematic or very precise reports that matches very specific requirements in just a few mouse clicks

apexaudit 11 SQL SERVER   Security Auditing With ApexSQL Audit

With ApexSQL Audit, SQL Server security auditing, but also any SQL Server auditing and compliance has never been more reliable, easier and simple. There’s quite a bit more to ApexSQL Audit than what’s outlined here, but this easily show how ApexSQL Audit is capable of meeting most of auditing concerns directly out of the box.

ApexSQL Audit shares other ApexSQL solutions easiness of installation, setup and administration. ApexSQL Audit is focused on ensuring requirements for easy and comprehensive auditing and compliance for SQL Server. The most impressive feature of ApexSQL Audit is the fact that it is a lightweight, it uses central repository database with the built-in tamper proofing mechanism and unprecedented ability to achieve high precision auditing, alerting, and reporting.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to Roll Back SQL Server Database Changes

In a perfect scenario, no unexpected and unplanned changes occur. There are no unpleasant surprises, no inadvertent changes. However, even with all precautions and testing, there is sometimes a need to revert a structure or data change.

One of the methods that can be used in this situation is to use an older database backup that has the records or database object structure you want to revert to. For this method, you have to have the adequate full database backup and a tool that will help you with comparison and synchronization is preferred.

In this article, we will focus on another method: rolling back the changes.

This can be done by using:

  • An option in SQL Server Management Studio
  • T-SQL, or
  • ApexSQL Log

The first two solutions have been described in this article

The disadvantages of these methods are that you have to know when exactly the change you want to revert happened and that all transactions on the database executed in a specific time range are rolled back – the ones you want to undo and the ones you don’t.

How to easily roll back SQL Server database changes using ApexSQL Log?

The biggest challenge is to roll back just specific changes, not all changes that happened in a specific time range.

While SQL Server Management Studio option and T-SQL read and roll forward all transactions in the transaction log files, I will show you a solution that finds and scripts only the specific changes that match your criteria. Therefore, you don’t need to worry about all other database changes that you don’t want to roll back.

ApexSQL Log is a SQL Server disaster recovery tool that reads transaction logs and provides a wide range of filters that enable you to easily rollback only specific data changes.

First, connect to the online database where you want to roll back the changes.

apexsql dbchange1 SQL SERVER   How to Roll Back SQL Server Database Changes

Once you select the database, ApexSQL Log will show its recovery model. Note that changes can be rolled back even for a database in the Simple recovery model, when no database and transaction log backups are available. However, ApexSQL Log achieves best results when the database is in the Full recovery model and you have a chain of subsequent transaction log backups, back to the moment when the change occurred.

In this example, we will use only the online transaction log.

apexsql dbchange2 SQL SERVER   How to Roll Back SQL Server Database Changes

In the next step, use filters to read only the transactions that happened in a specific time range.

apexsql dbchange3 SQL SERVER   How to Roll Back SQL Server Database Changes

To remove noise, it’s recommended to use as many filters as possible. Besides filtering by the time of the transaction, ApexSQL Log can filter by the operation type:

apexsql dbchange4 SQL SERVER   How to Roll Back SQL Server Database Changes

Table name:

apexsql dbchange5 SQL SERVER   How to Roll Back SQL Server Database Changes

As well as transaction state (committed, aborted, running, and unknown), name of the user who committed the change, specific field values, server process IDs, and transaction description.

You can select only the tables affected by the changes you want to roll back. However, if you’re not certain which tables were affected, you can leave them all selected and once the results are shown in the main grid, analyze them to find the ones you to roll back.

When you set the filters, you can select how to present the results. ApexSQL Log can automatically create undo or redo scripts, export the transactions into an XML, HTML, CSV, SQL, or SQL Bulk file, and create a batch file that you can use for unattended transaction log reading.

In this example, I will open the results in the grid, as I want to analyze them before rolling back the transactions.

apexsql dbchange6 SQL SERVER   How to Roll Back SQL Server Database Changes

The results contain information about the transaction, as well as who and when made it.

apexsql dbchange7 SQL SERVER   How to Roll Back SQL Server Database Changes

For UPDATEs, ApexSQL Log shows both old and new values, so you can easily see what has happened.

apexsql dbchange8 SQL SERVER   How to Roll Back SQL Server Database Changes

To create an UNDO script that rolls back the changes, select the transactions you want to roll back and click Create undo script in the menu.

apexsql dbchange9 SQL SERVER   How to Roll Back SQL Server Database Changes

For the DELETE statement selected in the screenshot above, the undo script is:

INSERT INTO [Sales].[PersonCreditCard] ([BusinessEntityID], [CreditCardID], [ModifiedDate])
VALUES (297, 8010, '20050901 00:00:00.000')

When it comes to rolling back database changes, ApexSQL Log has a big advantage, as it rolls back only specific transactions, while leaving all other transactions that occurred at the same time range intact. That makes ApexSQL Log a good solution for rolling back inadvertent data and schema changes on your SQL Server databases.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

This article shows how to use ApexSQL Refactor,a free SQL code formatting and refactoring tool. You can download ApexSQL Refactor, and explore it through the article.

ApexSQL Refactor is a free tool, for SQL code formatting and refactoring directly from SSMS or Visual Studio. You can qualify SQL Server object names, expand wildcards, or encapsulate SQL code. The add-in has nearly 200 formatting options and 11 code refactors. Using this tool allows you to locate and highlight unused variables and parameters. In addition, you can update all dependent database objects on renaming or changing columns and parameters. Besides SQL code in SSMS or Visual Studio, you can format SQL code from the external SQL scripts. The add-in integrates under the ApexSQL menu in SSMS or Visual Studio. To format SQL code inside SSMS or Visual Studio, select it in the query window and choose the ApexSQL default option, or other user- defined templates from the ApexSQL Refactor menu. In the same menu, you can find the Formatting options option:

image001 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Format SQL code

In the Formatting options section you can modify ApexSQL default formatting, or create your own formatting templates. Click the New button in the upper side of the window, and the new formatting template will be created. In the General tab, you can set indention, whether you are using spaces or tabs. You can set the wrapping to be applied to the lines longer than the specified number of characters, or add spacing inside/outside parenthesis, around operators (assignment, arithmetic, and comparison), and before/after commas.

Here you can manage empty lines, and set the placement for the opening and closing brackets:

image004 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

In the Capitalization tab, you can enforce the capitalization for SQL keywords, data type, identifiers, system functions, and variables. For each of the mentioned, you can choose from the drop down list whether it is going to be capitalized in upper case, proper case, or lower case.

image005 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

In the Comments tab you can manage comments, adding an empty line or border before/after the block comment. In addition, you can switch all comments to block/line comments, or remove all block/line comments:


image006 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Under the Expressions tab, you can set the formatting options for the operators (arithmetic, comparison logical). If you enable formatting for any operator, you will be able to set the minimum number of characters for the operator to apply formatting on. In addition, you can set the parenthesis placement, to move the operation to a new line, or to show the operations on multiple lines:

image007 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

In the Schema statements tab, you can setup formatting for the object definitions and parameters. For the object definitions, you can choose to place the body on a new line and to set the indentation. Parameters can be placed on a new line (aligned with keyword, or indented for a defined number of spaces). If there are more than one parameter, each one can be placed on a new line with a comma before/after the row:


image008 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

In the Data statements tab, you can set the options for column list, data statement, nested SELECT statements, and aliases. A column list can be placed on a new line, aligned with a keyword, or with the defined indentation. Each column can be placed in a new line, with a comma before/after it. A minimum number of characters can be defined for the data statements to be formatted. SQL keywords FROM and WHERE can be placed in a new line, aligned with keyword or indented.

For each nested SELECT statement, the first SELECT can be placed on a new line at the same position, aligned with SQL keyword, or indented. In addition, subsequent nested SELECT can be aligned with the previous SELECT.

All alias names can be aligned. The AS keyword can be used in all aliases in the SELECT statements, placed on a new line (aligned with keyword or indented):

image009 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

In the Joins tab, you can set the minimum number of characters for JOIN statement to be formatted. The first table can be placed on a new line (at the same position, aligned with previous keyword, or indented). Joined keyword can be placed at start/end of the line, or on a separate line with indention. ON keyword can be placed on a new line aligned with JOIN keyword, or indented. Nested join operations can be placed on separate lines, aligned with previous JOIN keyword, or indented:

image011 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

In the Value lists tab you can set the value list to be placed ona new line, aligned or indented. Earch row from the list can be placed in a separate line with comma before/after each row. Row values can be placed on a new line, aligned or indented. At the end, each value can be placed in a separate line, with comma before/after each row:

image012 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

In the Variables tab, you can set the variables to be placed on a new line, aligned with keyword, or indented. Each variable can be placed on a new line with a comma before/after each row:

image013 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

In the Flow control tab, you can set the condition keywords (WHEN, THEN, and ELSE) to be places on a new line and indented. In addition, you can enforce BEGIN and END keywords to be used in IF statements, and in stored procedures.

image014 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

To format SQL code outside SSMS or Visual Studio, click the Format SQL scripts option from the ApexSQL Refactor menu, and the Format SQL files dialog opens. Here you can browse your computer for SQL files, and apply specified formatting option (ApexSQL default, or user created template). You can overwrite an old SQL file, with a new one, or create new SQL file, and keep the old one

image015 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Split table

This option is used to split a SQL database table into two tables by copying or moving columns from the original table to a new one. It is useful when a table contains rarely used columns, they can be moved to another table, so the original table contains less frequently used columns. To split a table, right click on it in Object Explorer in SSMS or Visual Studio, and choose the Split table option from the ApexSQL Refactor menu to open
the Split table dialog. You can copy/move columns from the original table to a new one. For the new table you can define the name and schema. When you set the columns for the new table, you can preview the generated script, see the impact of changes and affected dependent objects:

image017 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Safe rename

Using this feature allows you to rename database objects without breaking the database dependencies, as it generates a SQL script for changing the object name and update all dependent objects. The Safe rename option can be applied to database tables, views, procedures, and functions. It applies to a table/view columns and function/procedure parameters. To rename any of the mentioned objects right click on it from the Object Explorer, or select it, and choose the Safe rename option from the ApexSQL Refactor menu. This will open the Safe rename dialog where you can enter a new name for the selected object, and clicking the Generate preview option, you can preview the script used to change the object name. If an error appears when renaming, it will be shown under the Warnings tab. The Sequence tab shows the process of renaming the object, listing the sequences that will be executed in order to rename the selected object:

image018 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Add surrogate key

When a primary key contains many columns, or it needs to be changed for any reason, a surrogate key is considered. Changing a primary key in database table requires updating all dependent object, in order to keep database functionality. To add surrogate key, select the table in Object Explorer and choose the Add surrogate key option from the ApexSQL Refactor menu. This will open the Add surrogate key dialog where you can choose the one of the existing keys, and specify the Surrogate column name value. The Generate preview option shows the generated SQL script in the preview section. All dependent objects, sequences, and warnings (if exist) will be shown under the appropriate tabs:

image020 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Change parameters

Stored procedure or function parameters can be changed by deleting and recreating, or using the ALTER statement. To change the parameters safely, use theChange parameters option in ApexSQL Refactor. Select the stored procedure or function parameter and choose the Safe rename option from the ApexSQL refactor menu. In the Safe rename dialog, change the parameter name, and generate the preview of a SQL script. Under the appropriate tab, you can review all dependent objects, warnings (if exist), and sequences that will be executed on renaming the parameter:

image021 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Replace on-to-many relationship

To use this option select the table from the Object Explorer and choose the Replace one-to-many relationship option from the ApexSQL Refactor menu. This will open a dialog where you can specify the name of the associative table, choose the dependent table, and a relationship. The Generate preview option generates a SQL script, which replaces a relationship. Under the appropriate tabs, you can review warnings, sequences, and dependent objects:

image023 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Copy code as

This option will convert SQL code into the supported programming languages. Supported languages are Java, VB.NET, C#, Perl, PHP, Delphi, Power Builder, Ruby, and C++. You can add additional template for other programming languages choosing the Customize languages option from the Copy code as submenu. To convert SQL code into any of the listed programming languages, point to a query window with SQL code you want to convert, and choose the language from the list. One you click the language from the list, open a new query window, and paste the created code:

image025 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

The Customize language template dialog allows you to edit templates for natively supported languages, or add new templates. Here you can enter a code that will be inserted before/after SQL code, define escape character for quotes, and preview the defined settings:

image027 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Unused variables and parameters

Parameters or variables declared or assigned a value, but never use or queried in any statements as UPDATE, EXECUTE, WHERE, INSERT or PRINT, is unused. ApexSQL Refactor can highlight unused SQL objects, and clean up SQL code. You can find unused objects inline, while typing SQL code. To find unused parameters and variables, run the Unused parameters and variables command from the ApexSQL Refactor menu:

image028 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

If there is any declared, but unused variables or parameters, ApexSQL Refactor will find them. To confirm that the highlighted parameter/variable is unused, bring the mouse pointer to it, and the tooltip comes up:

image030 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Object name qualifying

The Object name qualifying feature enables you to refactor SQL code in a way to add the owner (schema/user) of objects, object name, or alias name. When object name is qualified, SQL Server will not check if the current user is the owner of the object, which means fewer resources to use. As a result, query will be executed faster. The result of using this option can be easily reverted clicking the Undo in SSMS or Visual Studio. To apply the Object name qualifying option, choose it from the ApexSQL Refactor menu.

Encapsulate code as

Encapsulate SQL code means to use selected code as a database object, and makes it easier to reuse it. ApexSQL Refactor allows you to encapsulate SQL code as stored procedure, view, scalar inline function, or a table inline function. To encapsulate SQL code as one of the mentioned objects, select it in the query window, and choose the Encapsulate code as option from the ApexSQL refactor menu. Select the appropriate object, depending on what do you want to encapsulate, and the new window opens. Give a name and assign a schema to the selected object. If you click the Generate preview button, a SQL script will be created, and the parameters will be listed in the Parameters section. When everything is set, click the Encapsulate button:

image031 SQL SERVER   How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

Expand wildcards

This will allow you to expand wildcards e.g. “*” used in SQL, into column names. It affects the performance of a SQL query, as SQL Server will not expand them itself, before the query execution. If you type in the query:

SELECT * FROM Person.Address

After applying the Expand wildcard option, the above query will be transformed as follows:

SELECT Address.AddressID,
Address.AddressLine1,
Address.AddressLine2,
Address.City,
Address.StateProvinceID,
Address.PostalCode,
Address.SpatialLocation,
Address.rowguid,
Address.ModifiedDate
FROM Person.Address;

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Free intellisense add-in for SSMS

This article shows how to use ApexSQL Complete, a free SQL Server intellisense add-in. You can download ApexSQL Complete, and play along through the article.

ApexSQL Complete is a free SQL Server Management Studio and Visual Studio add-in that speeds up SQL coding. In this article, we will explain ApexSQL Complete through its features. To start using ApexSQL Complete, enable it from the ApexSQL menu under the main menu in SSMS:

image001 SQL SERVER – Free intellisense add in for SSMS

Hint list – complete your SQL code

This is a key feature of ApexSQL Complete. It helps you find the wanted object or a SQL keyword, and complete the SQL statement faster instead of typing the entire object name or keyword. After you start typing, for example “SE”, SQL intellisense will appear with all keywords and database objects that contains “SE”, listed by importance:

image002 SQL SERVER – Free intellisense add in for SSMS

You can uncheck the box for the appropriate objects, and prevent them to appear in the SQL intellisense from the add-in options, under the Hints tab. This will decrease the number of hints in the SQL intellisense list, and speed up the coding process:

image003 SQL SERVER – Free intellisense add in for SSMS

Another useful property of the SQL intellisense in ApexSQL Complete is a multiple sections, which allow you to navigate over the database schema, to a table, and select the specific columns, checking the appropriate boxes. You can also select the columns and hit the Enter key to insert them:

image004 SQL SERVER – Free intellisense add in for SSMS

If a SQL script you are using is large, at some point you will need to look at SQL code before you continue. ApexSQL Complete offers you to accomplish this without a break. Press and hold the CTRL key, and the SQL intellisense will become transparent, so you can see through, and review SQL code. Releasing the CTRL key will get you back to a previous state, and you can continue typing:

image005 SQL SERVER – Free intellisense add in for SSMS

Tab navigation – monitors all opened SSMS tabs

This feature allows you to track opened and recently closed tabs, or to restore previously saved session after crashing. These operations can be managed from the add-in options, under the Tab navigation tab. Here you can set the period for keeping the tabs saved, and set the interval for auto-save.

image006 SQL SERVER – Free intellisense add in for SSMS

This could be useful if, for any reason SSMS crashes. The Tab navigation feature saves your time, and gets you back to a point before crashing.

The Tab navigation pane consists of two parts, Opened tabs and Recently closed tabs. In the Opened tabs section, all opened tabs from one session will be shown, and you can easily switch from one to another. You can search the content of the opened and closed queries, and open the query that contains searched results. Double-clicking the query from a list of the Recently closed tabs tab opens it in a new query window. For the opened and closed tabs, if you select a query from the list, a complete content will be shown in the preview section to the right:

image007 SQL SERVER – Free intellisense add in for SSMS

At any point, you can save your workspace or opened tabs, and restore to the saved state later, if SSMS crashes.

Code structure – view and find SQL code blocks

This feature provides a tree-like form of SQL code presented in a separate SSMS window on the left side of the query window. When you enable the Code structure feature from the add-in options, it allows you to see all the important parts of SQL code used in the query. SQL code blocks from the Code structure window can be expanded so you can navigate to a specific part of the block. Selecting any item from the Code structure window highlights a SQL code block in the query window:

image008 SQL SERVER – Free intellisense add in for SSMS

This way you can move through SQL code in blocks and find the part you are looking for instead of scrolling down the query.

Executed queries – track executed queries

Using this feature allows you to track all executed queries in a defined period. To enable the feature, select the Log execute queries option under the Executed queries tab in the add-in options. You can set the folder for storing the executed queries. The queries are saved as an .xml files. You can also define the maximum number of lines in SQL code, which will be stored. The Default period option allows you to show the queries executed in a defined period.

image009 SQL SERVER – Free intellisense add in for SSMS

When activated, the Executed queries form will show all the queries executed in a defined time range. If you select a query from the list, its content appears in the preview section. You can search through the queries, executed in a defined period. Double-clicking any of the executed queries from the list opens it in a new query window in SSMS, so you can additionally change SQL code.

image010 SQL SERVER – Free intellisense add in for SSMS

Snippets library – insert often SQL statements

With this feature you can insert often used SQL statements, even a whole procedure, or blocks of SQL code. You can create a snippet from the ApexSQL Complete options, or from the SSMS query window:

1) To create a snippet from the ApexSQL Complete options, navigate to the add-in options, and click Add new snippet option, under the Snippets tab:

image011 SQL SERVER – Free intellisense add in for SSMS

Here you can edit any of the predefined snippets from the library, export/import them to use it on another machine.

2) To create a snippet from the SSMS query window, type SQL code you want to be defined as a snippet, select it, and right click on it. From the context menu, choose the New snippet option:

image012 SQL SERVER – Free intellisense add in for SSMS

This will open the Create a new snippet window, with the selected code already inserted in the Code section. You just need to define a name for the new snippet, and optionally a description:

image013 SQL SERVER – Free intellisense add in for SSMS

To use already created snippet from the Snippet library, click the Insert snippet option from the context menu in the SSMS query window, and double click on a snippet from the list to use it in the query.

Navigate to object – locate an object in the Object Explorer

This allows you to locate the selected object in the Object Explorer pane. In the query window, select the object you want to locate, and right-click on it. From the context menu, choose the Navigate to object option, and the selected object will be located and highlighted in the Object Explorer pane to the left.

image014 SQL SERVER – Free intellisense add in for SSMS

Test mode – execute queries without impact to the database

The Test mode feature allows you to execute a query in a test environment, without impact and consequences to the database. To use the Test mode feature, select the Test mode option from the toolbar, and highlight SQL code in the query window you want to execute.

image015 SQL SERVER – Free intellisense add in for SSMS

The Test mode feature will add BEGIN TRANSACTION and the ROLLBACK TRANSACTION statements. After the execution, it rolls back the transactions at the beginning:

image016 SQL SERVER – Free intellisense add in for SSMS

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Documentation Tool

This article explains how to use ApexSQL Doc, a SQL documentation tool. You can download ApexSQL Doc, and follow along with this article.

ApexSQL Doc is a SQL Server database development tool, for generating database and SSIS package documentation. It generates documentation in various output formats such as chm, html, doc, and docx. You can include relationship diagrams in a documenting process and customize the preview settings. ApexSQL Doc shows extended properties for all database objects created at the SQL Server level. Using the Extended property editor feature you can manipulate with previously created description or add a new one.

You can choose database objects to include in the documentation, along with the appropriate attributes. Documentation process can be scheduled and automated using the Command Line Interface (CLI). Available output styles can be applied to the output documentation file, or edited additionally.

A new version of ApexSQL Doc 2014 supports SQL Server 2014 and Integration Services 2014. It has a new UI style. Starting from ApexSQL Doc 2014 R2, Windows Azure will be supported, and PDF will be added as a new output format.

Starting the application will open main window where you can start a new project. Clicking the New buttonallows you to add an SQL Server instance, or Integration Services package. You can add multiple SQL Servers as well. To add a SQL Server instance or SSIS package, click the appropriate option marked in the image below:

image001 SQL SERVER – Documentation Tool

SQL database documenting

If you choose to connect to SQL Server, the Add SQL Server dialog opens. You can specify local server, or type the address for a remote one, and choose the authentication method. In the Connection options dialog you can increase the values for the Connection timeout and Execution timeout options if you have a slow connection with a remote server.

image002 SQL SERVER – Documentation Tool

When a SQL Server is connected, a list of the databases will be shown in the Databases section, where you can select the databases to include in the processing. Check the box for the database you want to add, or select multiple databases from a multiple SQL Servers, to document them at once:

image003 SQL SERVER – Documentation Tool

ApexSQL Doc allows you to document server objects. For each combination of the selected server objects, the bitwise set number is generated, and can be used in the CLI, as this number represents a specific object combination, so you don’t have to include the objects manually. For the highlighted object type, a list of the object instances is shown in the object filter section, where you can select a particular instance, and include it in the documenting process. The selected combination of the server objects can be saved as an external file, clicking the Export button, and used later in another project:

image004 SQL SERVER – Documentation Tool

In the Database options section, you can set additional options related to database documentation. ApexSQL Doc allows you to document system objects, DDL scripts, and highlight SQL syntax in the output document. In this section, you can set the object grouping (by schema or filegroup), sort objects by the owner/schema section, or include the extended properties. In the Table of Contents section, you can include a Dictionary section for a parameter/column names. Empty sections, or nodes with no relevant metadata existing can be included/excluded from the documentation. Including system objects, DDL scripts, or T-SQL highlighting can slow down the process, as this is performance intensive options:

image005 SQL SERVER – Documentation Tool

The next step is to select the database objects to be documented. Checking the box for the appropriate object will include it in the documentation and at the same time will show the list of the object attributes, which you can choose to document. The same as for server objects, the bitwise set number is generated for each combination of the selected database objects, and can be used in the CLI, or the selection can be saved as an external file, and used later in another project:

image006 SQL SERVER – Documentation Tool

Server and database objects can be filtered using the Filter editor feature. To enable filtering for the specific object, select the appropriate Use filter checkbox, and the list of the objects will come up under the Object filter tab. Here you can choose the specific objects to document, selecting the related checkbox.

image007 SQL SERVER – Documentation Tool

For advanced filtering, click the “…” button, and the Filter editor dialog will open. Here you can specify the filtering conditions to apply to a selected object. Depending on the selected criteria, the objects that satisfy the logical conditions will be included or excluded from the documenting:

image008 SQL SERVER – Documentation Tool

ApexSQL Doc allows you to include graphical dependencies in the documentation, and to customize the dependency type and layout settings. Documenting graphical dependencies requires more hardware resources, and can slow down the documenting process. Changing the value of the Scale option will increase/decrease a scale of the graphical dependencies in the output document. This will allow you to improve a visibility of the graphical dependencies in the output document. Object dependencies can be presented as tables, checking the Include dependency list/tables option:

image009 SQL SERVER – Documentation Tool

Under the Styles tab, you can select the style to apply to the output document. Choose one of the predefined styles, or edit any of them manually to suit your needs. For a full preview of the output styles, the JavaScript option needs to be enabled for the current Internet zone. Any changes you made will be shown in the Preview section:

image010 SQL SERVER – Documentation Tool

Before you generate the documentation, you can edit database extended properties using the Extended property editor. ApexSQL Doc allows you to review any object description added at the SQL Server level, or to add a new description. To add a description click the “…” button, and the popup window will open. Adding the description in the Extended property editor saves it automatically in the database.

image011 SQL SERVER – Documentation Tool

Once you have everything set, you can generate the documentation, clicking the Generate button from the Home tab:

image012 SQL SERVER – Documentation Tool

SSIS package documenting

ApexSQL Doc allows you to document online (from SQL Server, or Integration Services) and offline (from the file system) SSIS packages. To add a package, under the Integration services tab, click the Add button. When the dialog opens, choose the appropriate package source. New version of ApexSQL Doc supports SSIS 2014. You can include multiple packages from multiple sources, and document them at once:

image013 SQL SERVER – Documentation Tool

When the SSIS packages are added, from the Package details tab choose the objects you want to document. You can include package source (*.dtsx) file as well:

image014 SQL SERVER – Documentation Tool

All output options (format, style) apply to SSIS package documenting as for the databases. When the SSIS packages documenting is set, click the Generate documentation button from the Home tab.

ApexSQL Doc allows you to automate the documentation process, using the Command Line Interface, and schedule it using SQL Server Job, or Windows Task Scheduler.

Output files

Using the Save as option in MS Word, you can save the documentation file as a PDF file. Currently available output formats are chm, html, doc, docx (pdf through docx).

Here are the examples of the generated documentation:

image015 SQL SERVER – Documentation Tool

image016 SQL SERVER – Documentation Tool

image017 SQL SERVER – Documentation Tool

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Server Data Compare Tool

This article shows how to use ApexSQL Data Diff, a SQL data compare tool. You can download it here, install, and play along.

At the beginning, let me say something about this database software.

ApexSQL Data Diff is a SQL Server development tool that is used to compare and analyze data stored in tables, or views, between SQL databases or their backups, and for simplifying data migrations between databases. It can present a detailed view of differences between objects and encrypted objects, and also a row level difference for each object.

When SQL Server data comparison is complete, you can automatically synchronize specified objects between databases, or select a specific row for synchronization. You can isolate the differences that are important with advanced filtering of objects and rows.

The new ApexSQL Data Diff 2014 comes along with SQL Server 2014 support and the new UI style. In the ApexSQL Data Diff Community edition (free), the user can compare and synchronize data in SQL Server Express and Windows Azure SQL databases.

Right after the program is started, a Project management dialog shows up. You can use it to create a new project, or open the previously saved one. If you select New, the New Project dialog opens.

1 SQL SERVER   SQL Server Data Compare Tool

The first option in the Project dialog window is for selecting a source and a destination data source type. You can select two data types from a list: Backup and Database. Also, this dialog offers an option to revert a source and a destination, and to clone information between them.

2 SQL SERVER   SQL Server Data Compare Tool

If you select Database as a data source type, the details you need to provide are login information, server name, and select a database from the drop down list of all available databases on the specified server. If you select Backup as a data source type, you need to provide a path to a backup file.

Beside the regular options, if you click on the Show advanced tabs button, three more tabs will appear: Object filter, Object mapping, and Additional scripts.

In the Object filter tab, there are various options to filter objects and views.

3 SQL SERVER   SQL Server Data Compare Tool

The first option is to edit the Comparison key, which uniquely identifies each single row in a table, or a view, for both the source and destination data sources. Click on the arrow in the Comparison key cell of the selected object, the drop-down menu will open, and you can select the Edit custom comparison key option.

4 SQL SERVER   SQL Server Data Compare Tool

The new window will appear with a list of the selectable comparison keys. By clicking on the check box on the left side of the Column name, you can check/uncheck all of the keys, or to select only the keys which are significant for SQL Server data compare.

5 SQL SERVER   SQL Server Data Compare Tool

This option can help you in a situation when the comparison process shows incomparable objects, because there are no unique indexes or primary keys, and when additional information is required.

You can also select which columns will be included for comparison process. With a click on the arrow in the Columns cell, for the selected object, a drop-down menu will appear.

6 SQL SERVER   SQL Server Data Compare Tool

There, you can select which columns you want to include in the comparison process. In this example, only one column is unchecked, and this will automatically change the number in the Columns cell by reducing the number to one.

You can use the Row filter option to enter the conditions for SQL Server data compare to filter the rows that meet given condition. In the following example, we’ll select the row with the object SalesTerritory, then select the Row filter cell, and click on the three dots button. This will open the Row filter dialog for SallesTerritory, in which you can enter any T-SQL condition.

7 SQL SERVER   SQL Server Data Compare Tool

Click on the Refresh row count button, at the bottom of this dialog, to get the Row count, which is the number of the rows thatmatch previously mentionedconditions.

The last option in object filter is the Filtereditor for the listed objects. By clicking on the Edit filter button, in the top right corner of the object list, a new dialog opens, and you can select custom filter criteria for the tables, or the views. You can include/exclude some of the objects for comparison. With the Edit filter option, you have the ability to specify custom conditions to exclude the irrelevant tables from SQL Server data compare.

8 SQL SERVER   SQL Server Data Compare Tool

Object mapping is used for a custom mapping of tables and indexed views with different names and schemas. Tables and indexed views with the same names and schemas are automatically mapped. You can perform custom table/column mapping. There are two cases for performing the custom table/column mapping:

1) To exclude automatically mapped tables/columns from the comparison

9 SQL SERVER   SQL Server Data Compare Tool

2) To include differently named SQL Server tables/columns with the same schema names

10 SQL SERVER   SQL Server Data Compare Tool

Finally, the Additional scripts tab allows you to use scripts before and/or after the synchronization script is run. You can choose not to use script at all, to use an embedded script, or to use a saved script.

11 SQL SERVER   SQL Server Data Compare Tool

At the end, you can save your selections to a project and use it again. To run this saved project, select it from the Project management dialog and it will load all previously saved settings.

When all options are set, you can click the Compare button. After comparing is done, compared objects are shown in the Main grid. There are several columns in the Main grid: Schema, Object type, Total rows, and difference information which contains: Missing, Identical, Different, and Additional records. Any of those columns can be used for sorting the results, just by clicking on them.

12 SQL SERVER   SQL Server Data Compare Tool

All compared objects are shown, along with basic information and a number of differences between compared objects. Furthermore, the row level differences are shown in the difference details pane, from which you can choose specific rows for the synchronization.

To display certain objects in the Main grid you can use the inline Filter row option. To enable it, go to the View tab, and select the Filter row option. This option is used for filtering compared objects based on different information, object name, total rows, or schema, by entering appropriate condition.

13 SQL SERVER   SQL Server Data Compare Tool

Working with the results grid can be easier by using some of the options, which are located in the View tab, under the Grid group.

14 SQL SERVER   SQL Server Data Compare Tool

There are buttons for grouping the comparison results. The results can be grouped by an object type (tables, views, procedures, etc.). The Collapse group and the Expand group options allow you to automatically collapse, or expand, all selected groups in the Main grid. The Multi column sort option provides more sorting options and you may select up to four columns by which the results are going to be sorted. The last button for adjusting the way the results are shown is Auto size columns, which is used to auto adjust the width of the columns.

The Difference details pane provides viewing and selecting row level differences for synchronization. It’s located beneath the Main grid. If you want to choose some specific rows for synchronization, you have to select the checkbox for those rows.

15 SQL SERVER   SQL Server Data Compare Tool

One of the options that difference details pane provides for users is to view only the rows with specific difference type, by selecting the tabs at the bottom of difference details pane. Those different types can be: Missing types – when records are in the source, but not in the destination database; Identical types – when records are in both the source and the destination database; Different types – when records are in both the source and the destination database, but are not identical; Additional types – when records are not in the source, but are in the destination database.

You can select desired objects, from the Main grid, or you can select all objects by going to the Home tab, and click the Check all button.

After all desired objects are selected, the synchronization process can be started with a click on the Synchronize button, from the Home tab. The Synchronization wizard will appear with the first step, where the source and destination data source are shown, along with the synchronization direction, which can also be reversed.

16 SQL SERVER   SQL Server Data Compare Tool

In the next step, you will be prompted to select the output options. You may choose to Create a synchronization script, or Synchronize a database.

One of the options is to save the synchronization script. The other option is to open the script in an editor and you may choose to open it with the Internal editor, or to assign the other one.

17 SQL SERVER   SQL Server Data Compare Tool

The final step is the Summary and warnings window, where you can see all actions that are going to be performed, along with the possible problems that might occur during the synchronization. You may group the actions by the execution order, by an object, or by the action type.

18 SQL SERVER   SQL Server Data Compare Tool

Finally, select the Create script button, and after you reviewed it, you may execute it.

19 SQL SERVER   SQL Server Data Compare Tool

In ApexSQL Data Diff, you can create reports based on obtained differences, by selecting the Export option from the Home tab. You can export results in three ways: as an HTML plain report, an HTML report, and a CSV data export.

20 SQL SERVER   SQL Server Data Compare Tool

An HTML plain report, which has a simple structure, without user interaction, except when SQL scripts are included in the report. In this report you can see which data comparison options were checked/unchecked, which report options are selected, and data comparison results with all needed information. An HTML plain report is preferred for printing.

An HTML report is similar to an HTML plain report, by its look and presented information, but it also has expand/collapse region with data comparison results. In this way you can easily expand, or collapse, information that you want to see. This report is preferred for browsing.

21 SQL SERVER   SQL Server Data Compare Tool

The last of them is CSV data export, which can generate results into an XML file, for further detailed analysis. An XML file allows using the same document for different systems and platforms, where results can be further analyzed/used in different programs to inspect differences or creating some new report for objects.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SQL Server Schema Compare Tool

In this article, I’ll show how to use ApexSQL Diff, a SQL compare tool. You can download it here, install, and play along while reading this blog post.

But first, let’s say something about this database software.

ApexSQL Diff is a SQL Server tool that is used to analyze and compare object schemas between two SQL databases, and also to synchronize particular, or all differences between them. It is used for SQL Server schema compare and synchronize the following data types:

  • Databases
  • Backups
  • Script folders
  • Snapshots
  • Source control

It can also be used to repair or roll back changes from backup files, which can be very useful if you made some unwanted changes.

The new ApexSQL Diff 2014 comes along with SQL Server 2014 support and the new user interface style. In the free, Community edition, the user can compare and synchronize SQL Server Express and Windows Azure SQL databases.

Right after the program is started, there is a Project management dialog, where you create a new project, or open the previously saved one. If you select New, the New Project dialog opens.

image001 SQL SERVER   SQL Server Schema Compare Tool

The first option in this dialog window is for selecting source and destination data source types. You can select data types from a list: Backup, Database, Script Folder, Snapshot, and Source Control. The Project dialog offers an option to revert a source and a destination, and to clone information between them. There is also an option to export data source to a snapshot file, a script folder, or to source control.

image002 SQL SERVER   SQL Server Schema Compare Tool

When you select Database as data source type, the information you need to provide are server name, login information, and select a database from the drop down list of all available databases on the specified server.

If you select Backup or Snapshot as a data source type, then you need to provide a path to a file.

image003 SQL SERVER   SQL Server Schema Compare Tool

By choosing the Script folder option, apart from the script folder location, you have an option to Configure mappings, to set a default Collation, and a SQL Server version.

image004 SQL SERVER   SQL Server Schema Compare Tool

When you select the Source control option as a data source type, there are Source control settings which are in wizard form, and will guide you through setting up Source control type, login information, and configuring mapping. There are also options to set the Default collation and the SQL Server version

image005 SQL SERVER   SQL Server Schema Compare Tool

Apart from the regular options, if you click on the Show advanced tabs button, three more tabs will appear: Schema mapping, Object filter, and Additional scripts.

In the Schema mapping tab you can specify how owners/schemas are handled during the SQL Server schema compareprocess, by custom mapping schemas in a source and a destination database. You can also export/import mappings to save/load previously defined/exported mappings. Owners/schemas with the same name will be automatically mapped to each other.

image006 SQL SERVER   SQL Server Schema Compare Tool

To select specific objects for a database comparison, you can use the Object filter option. Here, all object types are shown in a detailed list, so you can choose the ones you want to include in the SQL Server schema compare process.

image007 SQL SERVER   SQL Server Schema Compare Tool

By choosing each object, it will appear in the first column of the Object filter tab, and clicking on it, a new window will show all objects of that type, which you may choose to include/exclude from the SQL Server schema compare process.

image008 SQL SERVER   SQL Server Schema Compare Tool

Lastbutnot least, the Additional scripts tab allows you to use scripts before and/or after the synchronization script is run. You can choose not to use it, to use an embedded script, or to use a saved script.

image009 SQL SERVER   SQL Server Schema Compare Tool

At the end, you can save the selections to a project and use it again, and save time to make the selections again. To run a saved project, select it from the Project management dialog and it will load all previously saved settings.

When everything is set up, click the Compare button. The comparison results will be shown in the Main grid. There are several columns in the Main grid: Object type, Source schema, Source object, Destination schema, and Destination object. Any of those columns can be used for sorting the results, just by clicking on them.

image010 SQL SERVER   SQL Server Schema Compare Tool

In the View menu, there is the Multi column sort option for more advanced sorting. It allows you more sorting options and you may select up to four columns by which the results are going to be sorted.

There are several other buttons, in the View menu, which can be used to adjust the way the results are shown. You can set an alignment to left, or centered. There are buttons for grouping the comparison results. The results can be grouped by a different type (not equal, missing, added, or equal), by an object type (tables, views, procedures, etc.), or no grouping at all.  There are two more buttons, Collapse group and Expand group, which allow the user to automatically collapse of expand all selected groups in the Result grid. The last button for adjusting the way the results are shown is Auto size columns, which is used to auto adjust the width of the columns.

image011 SQL SERVER   SQL Server Schema Compare Tool

When you select an object from the Main grid, differences between the object scripts can be seen in the Script difference pane. This pane is divided on two sides. The source script is shown on the left side, and the destination script is shown on the right side. Both scripts are scrolled together, regardless on which side you’re scrolling, so you can easily see the differences in code between them. Different lines of the scripts are highlighted, so they stand out from the rest of code, which makes them easier to find.

image012 SQL SERVER   SQL Server Schema Compare Tool

You can access to several options in the Script difference pane from the context menu. By default, the Enable syntax option is selected. It shows all SQL reserved words in a different color. The View whitespace option shows all formatting symbols used in the code, a new line, tab, etc. The Line numbers option is used to quickly find a certain line of code. Finally, there is an option to select different font sizes for displaying code in this pane. The default setting is Medium, but it can be changed to Large or Small.

On the far left side of the pane, there is a bar (marked red in the picture) with blue lines that represent positions in the script where the differences are.

If you want to limit the SQL Server schema compare to the specific objects in a database, you can use the Object filter feature. Select the wanted SQL object, and an icon of the Object filter will appear. By clicking on the icon, a new dialog opens, and you can select a custom filter criteria for that object. You can include/exclude some of the objects in the SQL Server schema compare. With the Filter editor option, you have the ability to specify custom conditions to include the relevant tables:

image013 SQL SERVER   SQL Server Schema Compare Tool

After you have selected all desired objects, you may start the synchronization process with the click on the Synchronize button. The Synchronization wizard will appear with the first step of synchronization direction, where the source and destination data sources are presented, with an option to reverse the direction of the synchronization.

The next step will show you all dependencies for the selected objects, with an option to include or exclude them from the synchronization.

image014 SQL SERVER   SQL Server Schema Compare Tool

In the Summary and warnings window you’ll see all actions that are going to be performed. You may group them by the execution order, by an object, or by the action type.

image015 SQL SERVER   SQL Server Schema Compare Tool

In the final step, you are asked to select the output options. You may choose to Create synchronization script, or to Synchronize now.

If you select the Create synchronization script option, there are options to save the script to a file, where you can specify a folder and a file name, and to open the script in an editor. You can use the internal script editor, which is a default option, or assign another one.

Choosing the Synchronize now option, will also give you the option to save the synchronization script to a file, and to create a backup of a database before the synchronization. For creating a backup, you can choose the default directory, or select some other folder.

image016 SQL SERVER   SQL Server Schema Compare Tool

After the script is created and reviewed, you may execute it.

image017 SQL SERVER   SQL Server Schema Compare Tool

ApexSQL Diff provides several options for creating reports.

Under the Home tab, there is an Export button, where you will find all options for creating reports. You can export comparison results as an HTML plain report, an HTML report, and an XML structure report.

An HTML plain report is ideal for printing, while a HTML report is designed for electronic browsing since it contains toggles that expand and collapse groups.

After you select one of these reports, it will be generated and presented.

image018 SQL SERVER   SQL Server Schema Compare Tool

Reference: Pinal Dave (http://blog.sqlauthority.com)