SQL Server Portal

Comparing files is one of the frequent activities, when we want to see the difference between two files. SQL developers face this problem on a day to day basis when they have two versions of the same file and they want to know where they made changes.

Fortunately, SQL Server Management Studio (SSMS) has this toolbar, which you can use to compare two files very easily.

In order to explain it, let me create two scripts as shown below.

Sample:

The below scripts will be saved as Version1.sql & Version2.sql.

• Version1:

USE Northwind
GO
 
CREATE OR ALTER VIEW [dbo].[OrdersQry] 
AS
   SELECT Orders.OrderID
        , Orders.ShipName AS ShipName
   FROM Customers 
   INNER JOIN Orders 
   ON Customers.CustomerID = Orders.CustomerID;
GO

• Version2:

USE Northwind
GO
  
CREATE OR ALTER VIEW [dbo].[OrdersQry] 
AS
   SELECT COUNT(Orders.OrderID) AS TotalOrders
        , Orders.ShipName AS ShipName
        , Orders.ShipAddress AS ShipAddress
   FROM Customers 
   INNER JOIN Orders 
   ON Customers.CustomerID = Orders.CustomerID
   GROUP BY Orders.ShipName
          , Orders.ShipAddress;
GO

Now, let’s compare above mentioned both scripts (Version1.sql, Version2.sql) in SQL Server Management Studio (SSMS) as shown in below steps.

Step 1:

Let’s activate Compare Files toolbar by clicking on View menu then click on Toolbars, and in Toolbars, click on Compare Files option as shown below.

The Compare Files toolbar will appear as shown below.

Step 2:

In the toolbar, there is menu button on extreme left hand side which shows the mode of compare. We have 4 types of mode as described below.

• Side-by-side mode: It compares both files side by side. This is selected by default as shown below.

• Inline mode: It compares combined scripts.

• Left file only: It compares the first script.

• Right file only: It compares the second file only.

Step 3:

Open File Version1 and Version2 in SQL Server Management Studio (SSMS) as shown below.

Step 4:

Open Command Widow by clicking on View menu then click on Other Windows then select Command Widow Or use shortcut(Ctrl+Alt+A).

Step 5:

In the Command Window, type Command Tools.DiffFiles and give the file name (Version1.sql, Version2.sql) which needs to be compared and press Enter as shown below.

Tools.DiffFiles Version1.sql Version2.sql

Step 6:

Both files will be compared in side by side mode since it is selected by default and the output will be as shown below.

• Additional scripts in the second file (Version2.sql) are represented in GREEN color.
• Modified or deleted scripts from first file (Version1.sql) are represented in RED color.
• Scripts which are NOT changed are represented in NO Background color.

Please note that in case of any changes which are made to these files need to be saved again, we need to run the DiffFiles command again.

Conclusion:

Compare Files toolbar is one of the handy toolbars in SQL Server Management Studio (SSMS). Do let me know if you use it and found it helpful.

Read Full Post »