Usage of SQL Server Schema Compare v1.21.0 extention

Voldman, Eliya 20

Hello,

I am using ADS:

Version: 1.50.0 (system setup)

Commit: 49c73a84388731fec941dac8d938345ac16bd470

Date: 2024-11-17T22:41:05.327Z

VS Code: 1.82.0

Electron: 30.5.1

Chromium: 124.0.6367.243

Node.js: 20.16.0

V8: 12.4.254.20-electron.0

OS: Windows_NT x64 10.0.17763
I have to compare 2 tables in the same database. I installed and used this extension: SQL Server Schema Comparev1.21.0

I never used it before ... and I see that it allows to compare only databases but not tables.

My question:

could you suggest some workaround to use this extension for table comparison
could you suggest another ADS extension for tables comparison?

Thanks

Accepted answer

Erland Sommarskog 115.9K Reputation points MVP

2025-01-10T22:02:29.89+00:00

Script both tables and save to file, and then compare the files in a good diff tool, for instance Beyond Compare.

Full disclosure: I am not affiliated with Scooter Software, the vendor of Beyond Compare. But I am a very satisfied user for more than twenty years.
Please sign in to rate this answer.

1 person found this answer helpful.
Voldman, Eliya 20 Reputation points

2025-01-13T14:22:11.4233333+00:00

@Erland Sommarskog - good idea. I use for comparison WinMerge tool and it gave me result. I simply used to do it before in Toad for SQL client and it has this 'compare' utility which was very handy. Hence, I expected that ADS would have it too.

Appreciate your help

@Erland Sommarskog - I noticed that when files I'm comparing have too many differences ... usage of any 'compare utilities' .. like WinMerge or ByondCompare are not handy. In this case I try to get 'only' the differences. That's why I used 'except' ... anyway ... thanks for good ideas
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

Bruce (SqlWork.com) 69,976 Reputation points

2025-01-10T21:06:52.8033333+00:00

why wouldn't you just use a full outer join query to compare?
Please sign in to rate this answer.
Voldman, Eliya 20 Reputation points

2025-01-13T14:16:16.31+00:00

@Bruce (SqlWork.com) this is good idea. I tried and it worked but Result Set was not straightforward: I mean in cases when new record was inserted ... I was able to locate it based on NULL but when only one of the field was change ... it's not clear how to locate it. I found that EXCEPT worked better ... although I had to run it twice in two directions. Anyway ... thanks for idea. Well ... my goal is to get result using ADS (preferably in GUI and easy interpreting ...)

Erland Sommarskog 115.9K Reputation points MVP

2025-01-13T21:57:51.2633333+00:00

So you wanted to compare the data? Since you said schema compare, I assumed that you wanted to compare, well, the schemas.

Yes, EXCEPT in two directions is what I often use when I'm lazy.

When there are too many differences for that to be workable, I sometimes copy the result into one sheet each in Excel, and in a third sheet I set up a formula like this:

IF(Sheet!!A1 = Sheet2!A1;"";"XXX")

and then I copy that formula all over the third sheet.

Note: Depending on your regional settings, it is possible that the semicolons should be commas.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
LiHongMSFT-4306 30,286 Reputation points

2025-01-13T03:08:50.0333333+00:00

Hi @Voldman, Eliya

How about using SQL Server Data Tools (SSDT) to compare the data that is contained in two databases.

See: How to: Compare and Synchronize the Data of Two Databases with SQL Server Data Tools.

You can compare the data in a source database and a target database and specify which tables should be compared. The data can be reviewed to guide a decision about which changes to synchronize. Then you would either update the target to synchronize the databases or export the update script to the Transact-SQL editor or to a file.

See this doc for more details: Compare and Synchronize Data in One or More Tables with Data in a Reference Database.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.
Voldman, Eliya 20 Reputation points

2025-01-13T14:30:36.1433333+00:00

LiHongMSFT-4306 - is SSDT part of ADS or it's an extension? I am asking because my ADS is installed on my server and I have full permissions but I don't have control on MS SQL Studio and can't install it there ...

Dan Guzman 9,251 Reputation points

2025-01-13T18:02:10.8633333+00:00

@Voldman, Eliya , the SQL Server Schema Compare extension for ADS from Microsoft provides the same schema compare functionality as SSDT for Visual Studio.

Erland Sommarskog 115.9K Reputation points MVP

2025-01-13T21:48:04.1666667+00:00

In any case, it appears that Cosmog did not read the question. With SSDT you can compare databases, but not equally easily two tables in the same database.

But beside that, I would say that it is immaterial what you have installed on your server. Normally, you don't run these tools from the same machine as SQL Server runs on. (Unless you have a local environment on your developer PC.)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Usage of SQL Server Schema Compare v1.21.0 extention

2 additional answers

Your answer