SQL SERVER – How to Survive on Free Tools for Month – SQL Data Compare Software – The ApexSQL Diff Tool

Note: This blog is based on ApexSQL Data Diff, download it and play along with is this blog post.

In the previous article I wrote about comparing two Windows Azure SQL databases using free community version of ApexSQL Diff. In this article we will write about comparing data in two Windows SQL Azure databases using a free Community version of ApexSQL Data Diff – a tool for SQL data comparison and synchronization.

Same way as in ApexSQL Diff, in ApexSQL Data Diff there is no difference in comparing SQL Server and SQL Azure databases.

In the Project dialog choose the Database as the destination and source and choose the Azure server.

You can additionally filter and map objects using advanced features so that only specific objects would be included in the comparison. When all that is set and done, you can begin with comparing objects.

Using the Advanced tabs you can filter database objects using the Object filter feature. You’ll get a list of objects for comparison to select/deselect objects that you want to compare.

After the SQL data comparison is done, compared objects are displayed in the easily accessible results grid. Each compared object is shown, with basic information and a number of differences in data between two compared objects. Also, you can see row level difference details in the difference details pane, which allows synchronization of specific rows.

One of the options for displaying certain objects in results grid is inline row filter. To use this option it’s necessary to go to the View tab, and to select a filter row in the show panels group. An inline row filter is used for filtering compared objects based on different information, object name, total rows, or schema.

Using the Grouping option will group compared objects by object type. Multi column sorting is used for sorting results by individual columns, by clicking each column header. One more option is filtering by different type which is located at the bottom of results grid. Filtering by difference type is present only different, identical, or incomparable objects in the results grid.

To synchronize detected differences user can select specific objects, from result grid, for synchronization, or he can select all objects by going to results group in the home tab, and clicking on check all.

The Synchronization wizard will start, and setting up is consisted of three steps. Selecting the data source that will be changed is the first step of choosing the synchronization direction. Synchronization wizard is giving an option to reverse the process as it is shown on the picture bellow.

The next step is selecting an option how to process synchronization script. User can perform two output actions: creating a synchronization script, or synchronizing a database. The script can be saved to file by choosing folder and file name. One of the options is to open script in editor and choosing where to show synchronization script.

Summary and warnings is the final step, where you can preview the impact of synchronization script. A detailed list of actions that will be taken through synchronization process is shown, along with potential problems that might occur during synchronization. List of actions can be grouped in a three ways: 1) by execution order, 2) by object, 3) by action type.

After selecting creates script a window of the batch data synchronization script will appear, and all synchronization data can be seen.

Like in ApexSQL Diff, in ApexSQL data Diff you can create comprehensive reports based on obtaining differences by selecting format in the home tab, under action group, and choosing export. There are three ways to export results: HTML plain report, HTML report, and CSV data export

 

An HTML report is interactive and user-friendly format that allows easy navigation, expand/collapse regions containing table/view information.

Remember that this tool is free for all who are Windows Azure SQL Databases. More information can be found on their company blog Solution Center. As a matter of fact there are plenty solutions for various problems related to SQL Server, you might want to take a look.

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

About these ads

SQL SERVER – How to Survive on Free Tools for Month – Database Comparison Done Easily with ApexSQL Diff – The ApexSQL Diff Tool

Note: This blog is based on ApexSQL Database Comparison, download it and play along with is this blog post.

ApexSQL Diff is a SQL Server database comparison software with a free Community version and Windows Azure SQL database support. To compare Windows Azure SQL databases in ApexSQL Diff selects “Database” as the data source type and a destination. Select the SQL Server Authentication and type the SQL Azure server name, user and password. Select databases from the list of available databases and click the Compare button.

The differences will be shown in the main grid. There are several columns containing information: Source Schema, Source Object, Destination Schema, Destination Object, and Status. Any of those columns can be used for sorting the results, just by clicking on them. You can see that you have only the objects that are different, identical, or located on one source (Not Equal, Missing, Added, and Equal).

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

Apart from that option, in the View menu, there are several buttons which can be used to adjust the way the results are shown. These buttons are separated into three groups. The first group is for setting an alignment, which can be left or centered (default). The next group of buttons is used for grouping the comparison results. The results can be grouped by different type, by object type, or no grouping at all. Grouping the results is a very good option, because the user can easily separate the needed results, or for instance, create a clearer report. There are two more buttons, Collapse group and Expand group, which are only useful if you choose any grouping. They allow the user to automatically collapse of expand all selected groups in the Result grid.

The last button in the View menu is Auto size columns, which is used to auto adjust the width of the columns.

When you select an object from the Results grid, differences between their scripts can be seen in the Script difference pane. This pane consists of two parts. On the left side, the source code is shown, and on the right side, the destination code is shown. When you scroll any side, they will both scroll together so you can easily see the differences in code between them. All lines of script that differ, are highlighted, so they stand out from the rest of the code, which makes them easier to find.

There are several options for Script difference pane, which can be accessed from the right-click menu. There is an option for displaying line numbers so that users can quickly find a certain line of code that they need. Enabling View whitespace shows all formatting symbols used in the code, new line, tab, etc. Enable syntax option is selected by default. It shows all SQL reserved words in different colors, to separate them from the others in the 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.

To see more information on object differences, check the Differences by type pane, where you can easily see what the types of differences in the selected object are. This feature is very useful, because you don’t have to search the code to see what the type of difference is there.

In the Object Filter pane, you can select which objects are going to be compared. All object types are shown in a very detailed list with checkboxes, so you can choose the ones you want to compare only. More common objects are segregated from the less common ones.

To synchronize changes between databases select the objects for comparison in the main grid and click the Synchronize button.

You’ll be prompted with the Synchronization Wizard. By clicking the Reverse button you can reverse the direction of the synchronization.

Press the Next button and choose whether to include dependencies. You can see all the actions to be made in the process of synchronization.

In the output options you can choose to save the script to a file, or open it in an editor and review.

After reviewing you can execute the script.

ApexSQL Diff also has a reporting feature to document changes and generate comprehensive reports.

Note: This blog is based on ApexSQL Database Comparisondownload it and play along with is this blog post. If you want to find out more I suggest you visit SQL change management category on their Solution Center.

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

SQL SERVER – How to Survive on Free Tools for Month – “View dependencies” returning all results – The ApexSQL Search tool

Note: This blog is based on ApexSQL Search, download it and play along with is this blog post.

ApexSQL Search is a Free SSMS and VS add-in for SQL object and data text search, and it shows visual relationships between objects – in SSMS called “view dependencies” but here it is fully utilized and functional. A powerful feature indeed.

Beside visual representation of relationships it also has useful features for managing extended properties and smart renaming without breaking dependencies, and an automatic SQL query editor renaming feature

Using the Database object search feature you can find all SQL objects containing the specified phrase

Choosing the Database text search feature you can find data in tables and views that matches the specified phrase

You can customize the sorting by clicking the appropriate column name, or right-click to filter results using the Filter row.

You can also use the same wildcards supported by the LIKE operator in SQL Server and the search text with regular expressions. You can find all supported wildcards here. And read about text search text with regular expressions here.

The edit extended properties feature allows customizing extended properties stored in your database by calling the Edit extended properties from the ApexSQL Search menu. The Automatic query editor renaming feature has automatically named SQL query windows based on their contents.

With the smart rename feature you can rename your SQL objects without breaking dependencies between them. To open the Smart rename dialog select an object in the Object Explorer and choose the Smart rename command from the ApexSQL Search menu or right-click the object in the search results and choose the smart rename command

Enter the new name of the object and click Generate preview to see the generated script. In the Warnings tab you can see all warnings. The Sequence tab you can see the actions in the order they will be performed. The Dependencies tab is a list of dependent objects that refer to an object which will be renamed.

To see dependencies of an object, right-click an object in the SSMS Object Explorer, and choose the View Dependencies command. Another way is to select an object from the Object Explorer and select the same command from the ApexSQL Search menu

The View Dependencies feature visualizes all objects’ relationships, and allows customization of the arrangement of the dependency diagram. You can control how this feature will arrange generated dependency diagrams with the Dependency layout option

Circular – objects are displayed in a circle around the object to which they are associated

Force directed – the most commonly used objects are closer to the center of the diagram

Hierarchies – objects are organized by generations, so that parents have at the top of the diagram and all objects of the same generation the same hierarchical level

Orthogonal – objects are set at a right angle relative to one another in the diagrams

This blog is based on ApexSQL Searchdownload it and you can experiment with the concepts which I have discussed in this blog post.

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

SQL SERVER – How to Survive on Free Tools for Month – Enjoy Refactoring – ApexSQL Refactor

Note: This blog is based on ApexSQL Refactor, download it and play along with is this blog post.

ApexSQL Refactor is a free SQL Server Management Studio and Visual Studio add-in for database refactoring and SQL code formatting.

ApexSQL Refactor has 11 databases and SQL code refactors including:

  • Smart rename – this database refactoring feature generates a SQL script which will change the object name and update all the dependent objects, without breaking dependencies. It applies to: procedures, functions, tables, and views, table/view columns and function/procedure parameters
  • Split table – If you have some more frequently used columns and some less frequently in one table, you can apply this feature to split a table into two tables to achieve performance gains
  • Add surrogate key – use this feature to replace the natural key with the surrogate key
  • Change parameters – Change, add or remove stored procedure and function parameters without breaking dependencies
  • Encapsulate as :
    • Stored procedure
    • Scalar Inline function
    • Table Inline function
    • View
  • Expand wildcardsuse this feature to be sure that you’ll avoid SQL mistakes as using SELECT * in your code and to list all wanted columns without the need of remembering all 40+ columns in your table 
  • Qualify object names – qualifying object names are important for avoiding code ambiguity and for performance gain in saving optimizers time and for reusing execution plans
  • Replace One- to-many relationship with Associative Table

One-to-many relationship is a relationship between two entities in a database where one entity has a multivalued relationship with another. This relationship sometimes must be changed if there are some additional requests in database design. One approach is using association table. This can be achieved by using the ApexSQL Refactor’s Replace One- to-many relationship with Associative Table feature.

Selecting the table in the Object Explorer and choosing the Replace One-to-many Relationship option from the ApexSQL Refactor menu opens the Replace One to many relationship with the Associative Table dialog.

In the Associative table name text box enter the name of the association table and click on the Generate preview button. You can review the changes, and check warnings, dependencies and sequence of the script and changes to be done. You can choose to replace immediately by clicking the Replace now button, or click the Open script button to open the script in the query window. 

Beside refactor features the tool also has another two useful features:

Copy SQL code as - to pack your SQL code in a format suitable for one of the offered programming languages​​ or make your own template. Default languages are: Java, VB.NET, C#, Perl, PHP, Delphi, Power Builder, Ruby, and C++ 

Find unused variables – to find the variable and parameters that are only declared, but never used

In addition to refactoring features there are more than 160 SQL code formatting options. The formatting options include parentheses, commas, capitalization, data statements, schema statements, expressions, a many more formatting options.

One great option is the separate option for formatting the JOIN statements. It offers various options of placing the JOIN object on a new line, placing ON clause on a new line with custom indentation, placing nested operations on a separate line, and more.

Best of all is that every change you make will be shown in the preview window marked the red. For example selecting the Place joined object on a new line option.

Another useful option is the possibility to format more SQL scripts and even folders containing SQL scripts at once. By choosing the Format SQL Scripts feature open the Format SQL files dialog and choose the folders and .sql files.

Enjoy refactoring your database and making your code pretty by any standard you follow and use the possibility to have more formatting templates at once. All that for free with SQL formatter and refactor from ApexSQL. You can read more about it over at ApexSQL Solution Center.

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

SQL SERVER – How to Survive on Free Tools for Month – ApexSQL Complete – A Free SQL Advanced Intellisense

Note: This blog is based on ApexSQL Complete is a SQL intellisense, download it and play along with is this blog post.

ApexSQL Complete is a free add-in for auto-completing SQL code which integrates with SQL Server Management Studio  and Visual Studio.

The auto-complete feature recognizes, and predicates SQL keywords as well as objects, schemas, databases, parameter and variable names. To auto-complete SELECT statement in a single click just type the SELECT statement into the query window and check the column in the hint list. The rest of the statement will be auto-completed.

By highlighting the object in the hint list you’ll get the complete DDL statement of the selected object even if it is encrypted.

Using the Insert full INSERT statement option you can get the full INSERT statement for a table, without being bothered to remember the order of values you need to pass and the need to write out all the columns. Simply type the INSERT statement in the query window and select the wanted table from the hint list.

The Insert Procedure Parameters Automatically options automatically to display input parameters of the stored procedure in the correct order, and to schema qualify object names automatically use the Insert Object Owner option.

ApexSQL Complete also has user friendly Snippets feature with various variables. I’ll show the snippets feature with a simple example using the $PROMPT$ variable, and make another way to complete statements in the query window.

Create a snippet or edit the existing one in the snippets dialog.

From the query window, right-click and choose the ApexSQL Complete Insert Snippet command. Select the snippet from the hint list, and in this case, because we’re using the $PROMPT$ variable it will prompt the Column Dialog we created.

Enter the variable name, in this simple example the column name and click Ok. The rest of the statement will be inserted and you will be prompted with the hint list for choosing the table to complete the FROM clause

ApexSQL Complete is a SQL intellisense that has more useful options and commands such as:

  • Choose the Error list feature to get a list of all syntax errors, updated in real-time
  • Use Hints feature to expand or narrow the auto-complete hint list
  • With the Code structure feature to display the internal structure of your code
  • Create your own aliases or use auto created aliases. Custom table alias will replace the auto created one and it’ll be listed in the hint list
  • Add alias or object name to the columns in the WHERE clause, even if there is only one object is specified in the FROM clause, and many more

If you’re looking for a tool that can make your coding process faster and easier and within your budget, try out this free SQL assistant by ApexSQL

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