Tips & Tricks - Compare Database Project in Solution With SQL Server Database

Problem

Sometimes we need to add a database project to our project solution to manage the script and also for deployment purposes etc.

When multiple team members are working on a project and are continually adding scripts manually in the database project and a team member updates or adds a new object in the database and forget to update the script in the database project in the solution for any reason then when the database project is deployed it's very hard to find the recently added scripts and schema.

So here is the solution. 

Solution

Comparison, Comparison, and Comparison between database script and database.

And, how do do this comparison? It's very simple; it barely took 5 minutes or it depends on your database size.

Have a look.

Step 1

First, we will create a sample project of WPF and add a new database project to it.

SQL-Server1.jpg

Step 2

Now I will add a new database project to the solution and I will use the "Northwind" database as an example to add a script in this database project.

SQL-Server2.jpg

Step 3

SQL-Server3.jpg

 Step 4

SQL-Server4.jpg

Step 5

SQL-Server5.jpg

Step 6

SQL-Server6.jpg

Step 7

SQL-Server7.jpg

Step 8

SQL-Server8.jpg

Step 9

Then click "Start".

SQL-Server9.jpg

Now click "Finish".

A script will then be generated in our database project.

SQL-Server10.jpg

Now suppose we have created a new table "Test" in our database for development.

And we have forgotten to add the script "Test" table in our database project.

For this reason, we will have to face the issue at the time of deployment.

CREATE TABLE Test
(
       Id INT,
       TestData VARCHAR(50)
)

Now our new step is how to compare the script.

Step 10

SQL-Server11.jpg

Step 11

SQL-Server12.jpg

Step 12

SQL-Server13.jpg

SQL-Server14.jpg

Now click "OK".

You will get the result.

SQL-Server15.jpg

Conclusion

In the result above we can see that the selected rows showing in this database object "Test" is missing in our database project and in the following pane, we will see the script.


Similar Articles