25

I frequently need to compare SQL procedures to determine what has changed in the newest version. The problem is, everyone has their own style of formatting, and SQL doesn't (usually) care about where one puts their newlines (e.g. where clauses all on one line vs. newline before each AND).

This makes it very difficult (especially for long procedures) to see the actual differences. I cannot seem to find a free diff/merge utility that will allow me to ignore newlines (i.e. treat as whitespace). So far I've tried WinMerge and Beyond Compare without any luck. Does anyone know of a diff tool (ideally free) that would see these two examples as identical?

Ex. 1:

the quick
brown

Ex. 2:

the
quick
brown

Thanks in advance.

Josh Lee
  • 171,072
  • 38
  • 269
  • 275
Prometheus
  • 870
  • 1
  • 8
  • 14
  • There were no free utilities that answered this question. If an answer is added for a free utility, I'll give it the bounty. – Greg Nov 04 '10 at 13:54
  • 2
    The answer I accepted offers a free utility. ApexSQL Refactor is bundled with a bunch of their trial software, but you can choose to install just that one item. It integrates very well into SQL Server Management Studio and will fix a whole query or just the selected text by pressing Ctrl-shift-R. Besides making comparisons dead simple, it has the added bonus of standardizing your query formatting. Also, it is extremely customizable. – Prometheus Nov 10 '10 at 05:59
  • Maybe meld can do it with some settings? – Jonathan Mar 09 '20 at 18:44
  • I asked (and answered) this here : https://softwarerecs.stackexchange.com/questions/80584 In short: IntelliJ IDEA has this functionality – David Balažic Sep 19 '21 at 10:48

12 Answers12

11

I really like SourceGear's DiffMerge!

It works on all platforms and has built in rulesets, but allows you to create and add your own. Which means that you can ignore what you want, when you want it.

Bonus, it is free!

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
  • `View->Hide Unimportant Differences` seems to take care of it. Not sure I trust that setting, but I'll try tweaking the ruleset to my liking. :) I really like that there are two tabs for loaded/edited files. – Greg Nov 10 '10 at 18:22
  • The more I use this tool the more I like it. I just found that it can be integrated into Windows Shell and make is much more convenient. – Dustin Laine Nov 10 '10 at 18:28
  • I've tried it with two files about 2MB each and it doesn't work. I tried a new ruleset as well (ignore line endings). The two files are the same XML, one is a tidied up version that's all. Any other way to check? – Vijay Feb 27 '13 at 12:34
  • [This answer](http://stackoverflow.com/a/7161780/107537) helped me with my task! So WinMerge did work in the end for me!! Additionally this [online app](http://www.raymondhill.net/finediff/viewdiff-ex.php) from another [question](http://stackoverflow.com/a/5082839/107537) also worked for me, though much slower. – Vijay Feb 27 '13 at 12:52
  • I just tried DiffMerge_4_2_0_697_stable_x64 (the ZIP version) and even after turning on "Hide Unimportant Differences", it still shows all changes, like added tabs, added empty lines, etc. I am comparing *.txt files. Is there something I'm missing? – David Balažic Sep 15 '21 at 10:26
  • Edit: It is better when comparing java files, but even there it reports "a=b" to be different than "a = b", which again just a simple white space change. – David Balažic Sep 15 '21 at 10:32
7

What i've done in my own similar case is to use a sql prettifier which will organize two sets of semi-disparate SQL in very similar fashion automatically. i then paste and compare the results with WinMerge.

It's a two-step process but it's much more palatable than many other options, especially when many lines of code are involved.

Link to web-based Sql Pretty printer that's decent.

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • The tool in your first link was perfect for the job. It took a bit of doing to sift through all the trial software and just install the free SQL Server management Studio plugin, but this plugin allows me to reformat the procedures to have the same line breaks. Great Answer! Running the results through WinMerge now supplies usable results. – Prometheus Nov 20 '09 at 22:35
  • In case anyone still looking for this, to avoid the manual steps and have the reformatting happen automatically or semi-automatically, try my winmerge plugin to the open-source "Poor Man's T-SQL Formatter" library: http://www.architectshack.com/PoorMansTSqlFormatter.ashx#Download_8 – Tao Feb 23 '12 at 12:39
2

I love Araxis merge. Not free but well worth it. it can, among other things, ignore any kind of whitespace if you want.

Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
2

You can use The DTP (Data Tool Project) of the Eclipse IDE.

To show it I created two almost identical SQL files and let eclipse show me the differences. After clicking "show next" I took a screenshot.

As you can see it still highlights the newlines, but by the way it does you can immediately see that they contain no substantial change to the SQL. It's easy to spot where I changed the ID from 1 to 2.

Here's the result.

alt text alt text alt text

lothar
  • 19,853
  • 5
  • 45
  • 59
  • As a Visual Studio user, the more I see of Eclipse the more jealous I get. – Greg Nov 08 '10 at 14:08
  • 1
    Seriously? It makes me gag every time. I think I'd quit this business if I had to stare at Eclipse all day. The pixelated "un-anti-aliased" graphics bug me more than they probably should. – Prometheus Nov 10 '10 at 05:56
  • I don't know about that bug. I sit next to a guy who uses Eclipse all day long and I use VS all day long. I have to install a handful of plugins to get VS to the same level of functionality. – Greg Nov 10 '10 at 21:11
2

Compare++ is an option, you can try "Ignore code style changes" in the 'smart' menu. It support structured comparison for many langugages such as C/C++, JavaScript, C#, Java, ...

James Kolpack
  • 9,331
  • 2
  • 44
  • 59
winlts
  • 29
  • 2
2

Regardless on your definition of "Free" (beer vs speech/libre), Poor Man's T-SQL Formatter is also available to do this, either with WinMerge (using the winmerge plugin) or Beyond Compare and other comparison tools that allow for command-line pre-formatting, using the command-line bulk formatter.

If you prefer to take it for a whirl without downloading anything, it's available for immediate use online (like its non-libre counterparts T-SQL Tidy, Instant SQL Formatter, etc):

http://poorsql.com

Tao
  • 13,457
  • 7
  • 65
  • 76
1

You can use the command-line tool wdiff to ignore newlines. wdiff is a GNU tool for comparing files on a word-by-word basis. It can ignore newlines with the -n option.

Suppose I put your 2 example files into ex1.txt and ex2.txt. Then we can run:

$> wdiff -n ex1.txt ex2.txt
the
quick
brown

The output is actually the contents of the first file. Note that there are no + or - signs, which means the files have the same strings.

If I had added "fox" to the end of ex1.txt, then the output would look like this:

the
quick
brown [-fox-]

If seeing the common words still bothers you, you can add -3 or --no-common. Here's the example again where I added "fox" to the first file:

$> wdiff -n -3 /tmp/ex1.txt /tmp/ex2.txt

======================================================================
 [-fox-]
======================================================================
nofinator
  • 2,906
  • 21
  • 25
  • pointing out the obvioius, but notice how there are no line numbers. Makes it pretty worthless. – Jeff Oct 09 '17 at 16:42
  • 1
    There's a similar tool called [dwdiff](https://os.ghalkes.nl/dwdiff.html) that can print line numbers. The equivalent command to my last example plus newlines would be: `dwdiff -L -3 ex1.txt ex2.txt`. – nofinator Feb 01 '18 at 16:42
1

Our SD Smart Differencer compares two source programs according to their precise grammatical syntax and structure, rather than according to raw text. It does so by parsing (SQL) source the way a compiler would, and comparing the corresponding compiler data structures (e.g., abstract syntax trees). The SmartDifference consequently does not care about newlines, whitespace or intervening comments.

It reports differences, not in terms of line breaks, but rather in terms of programming language structures (variables, expressions, statements, blocks, functions, ...) and in terms close to programmer intentions (delete, insert, move, copy, rename) rather than line-insert or line delete.

SQL (like many other computer language names) is the name of a family of computer languages that are similar in syntax but differ in detail. So for the Smart Differencer, which dialect of SQL you are using matters. We have SQL front ends (therefore SmartDifferncers) for PLSQL and SQL2011. To the extent you SQL stays within the bounds of either of these, the Smart Differencer can work for you; to the extent you use extra goodies of SQL Server or Postgres, the SmartDifferencer presently can't help you. [We develop language parsers as part of our business, so I expect this is a matter of delay rather than never].

While the OP asked about SQL in the details, his headline question is language agnostic. There are SmartDifferencers already for many other widely used languages other than SQL too: C, C++, C#, Java, ...

Ira Baxter
  • 93,541
  • 22
  • 172
  • 341
1

Another alternative is Emacs' Ediff. Works great if you are not afraid of Emacs.

MarcH
  • 18,738
  • 1
  • 30
  • 25
0

PHPStorm's diff tool's "ignore white space: all" command does it perfectly as you want. And it has integrated support for many VCS like SVN, git, etc. As well as integrated SQL support!

Not free but time isn't free either. Want to waste time doing it the hard way? Go ahead.

I still can't believe it's 2014 and this wasn't a standard feature of all diff tools!!

BTW I believe WebStorm's diff tool would also work.

CommaToast
  • 11,370
  • 7
  • 54
  • 69
-1

Have you tried KDiff? I'm certain you can ignore whitespace with it, and if it's not powerful enough for you it allows you to run a preprocessor over the file. Best of all it's free and open source.

Chris Pfohl
  • 18,220
  • 9
  • 68
  • 111
-2

If you're on Windows, WinMerge is pretty slick. Under Linux (and maybe OS X), there's Meld.

Both are free as in beer and work pretty well. Not quite as cool as Araxis, but then we don't want you drooling on your desk.

Both are all-purpose diff tools with such features as white space ignoring. I'm not absolutely certain they ignore blank lines, but chances are good they can.

Carl Smotricz
  • 66,391
  • 18
  • 125
  • 167