For an instructor lead, in-depth look at learning SQL click below.
Databases are the backbone of any application. As such, maintaining and managing the database schema effectively is of utmost importance. There are often scenarios wherein we need to compare two SQL databases, either for syncing them or to identify the differences between them. This blog post will walk you through the process of comparing SQL Server Database Schemas and identifying the differences using SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL).
Comparison Using SQL Server Management Studio (SSMS)
Firstly, let’s take a look at how we can utilize SQL Server Management Studio for database schema comparison:
Step 1: Select Source Database
In SSMS, connect to the SQL Server instance of your source database. Then, right-click the database, select Tasks, then “Generate Scripts”.
Step 2: Select Target Database
Similarly, connect to your target database and follow the same steps as mentioned in Step 1.
Step 3: Compare Files
Finally, you have two SQL script files, one for each database. Now, you can use any text comparison tool to check the differences between these files.
Although effective, this process can be tedious if you need to compare databases often or if the databases are large.
Comparison Using T-SQL
By leveraging the power of the T-SQL language, we can create a query that highlights the differences between two schemas. An example of comparing two tables is shown below:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table1' AND COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table2') |
This script will return the column names which are present in ‘table1’ and absent from ‘table2’. To compare tables in different databases, simply prefix the TABLE_NAME with the respective database name.
Alternate Method Using EXCEPT
We can also use the EXCEPT operator in SQL to identify differences. Here’s an example:
|
1 2 3 4 5 |
SELECT COLUMN_NAME FROM DB1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table1' EXCEPT SELECT COLUMN_NAME FROM DB2.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table2' |
This query will also return the column names present in ‘table1’ of ‘DB1’ and absent from ‘table2’ of ‘DB2’.
Conclusion
While both SSMS and T-SQL provide solid solutions for comparing SQL Server Database Schemas, choosing one depends on your specific use-case. I hope this guide assists you in efficiently comparing and synchronizing your database schemas.