7

I'm comparing two SQL server databases (development and live environment, SQL2005 and SQL2008 respectively) to check for differences between the two. If I generate a script for each database I can use a simple text comparison to highlight the differences.

Problem is the scripts need to be in the same order to ease comparison and avoid simple differences where the order of the stored procedures is different, but their contents are the same.

So if I generate this from development:

1: CREATE TABLE dbo.Table1 (ID INT NOT NULL, Name VARCHAR(100) NULL)
2: CREATE TABLE dbo.Table2 (ID INT NOT NULL, Name VARCHAR(100) NULL)
3: CREATE TABLE dbo.Table3 (ID INT NOT NULL, Name VARCHAR(100) NULL)

And this from live:

1: CREATE TABLE dbo.Table1 (ID INT NOT NULL, Name VARCHAR(100) NULL)
2: CREATE TABLE dbo.Table3 (ID INT NOT NULL, Name VARCHAR(100) NULL)
3: CREATE TABLE dbo.Table2 (ID INT NOT NULL, Name VARCHAR(100) NULL)

Comparing the two highlights lines 2 and 3 as different, but they're actually identical, just the generate script wizard did table3 before table 2 on the live environment. Add in 100's of tables, stored procedures, views, etc. and this quickly becomes a mess.

My current options are:

  1. Manually sort the contents before comparison
  2. Create a program to create the scripts in a specific order
  3. Find a freeware application that sorts the generated scripts
  4. Pay for a product that does this as part of its suite of tools
  5. (Some other way of doing this)

Hopefully, I'm only missing the checkbox that says "Sort scripts by name", but I can't see anything that does this. I don't feel I should have to pay for something as simple as a 'sort output' option or lots of other unneeded tools, so option 4 should just be a last resort.

EDIT I have full access to both environments, but the live environment is locked down and hosted on virtual servers, with remote desktoping the typical way to access live. My preference is to copy what I can to development and compare there. I can generate scripts for each type of object in the database as separate files (tables, SP's, functions, etc.)

Improve this question
7
  • You're probably asking for trouble comparing database schemas by looking for text differences in the CREATE script, but maybe just use like Notepad2 or something to sort all the lines first? Commented Oct 10, 2011 at 15:36
  • @Mike: If he sorts all lines in the script alphabetically, how will he be able to tell what the different objects are? Commented Oct 10, 2011 at 15:43
  • Any file comparison tool, like "fc".. Commented Oct 10, 2011 at 15:44
  • Please edit your question - Are you able to actually create both schemas in a live database first? Commented Oct 10, 2011 at 15:45
  • @Mike: what i mean is, each scripted object goes across multiple lines of the script, so sorting the script alphabetically by line will jumble it beyond comprehension. E.g, in the 'c' section you will just have loads of lines saying 'create table foo' and no idea what columns are in that table. Commented Oct 10, 2011 at 15:48

5 Answers 5

Reset to default
8

Depending on your version of Visual Studio 2010 (if you have it), you can do this easily via the data menu, based on your original intent, you might save yourself some time.

Edit: Generating the actual DB's and then comparing the schema comparison tool as shown below is the same net effect as comparing two script files and you don't have to worry about line breaks etc.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I got the feeling the OP wanted to compare schema creation scripts, and didn't actually have the live databases to compare.
He could run both scripts, let them create two DB's and then compare schemas :) Same net effect
I've full access to both environments, but not from Visual Studio: Live is hosted on virtual servers and is (rightly so) locked down to prevent external access. Creating two databases is a bit of an overhead but is still another option.
So if you can script all 3, why not then CREATE them with different names on a TEST Sql Server you have access to and then compare a->b, b->c. If a=b and b=c you are fine, because a=c. Surely you can connect to a test database server internally?
2

Red_gate's SQLCompare is the best thing to use for this, worth every penny.

Improve this answer

Comments

1

This is quite hard to do with scripts - because SQL will tend to generate the tables/objects in the order that makes sense to it (eg dependency order) rather than alphabetical order.

There are other complications that come up when you start comparing databases - for example the names of constraint objects may be randomly generated, so the same constraint may have different names in each DB.

Your best bet is probably option (4) I'm afraid ... an evaulation copy of Red Gate Sql Compare - free for 30 days. I've used it a lot and its very good at pinpointing the differences that matter. It will then generate you a script to bring the two schemas back into sync.

edit: or Visual Studio 2010 Ultimate (or Premium) can do it apparently - see kd7's answer

Improve this answer

3 Comments

vs2010 premium has this inbuilt, I'm sure some other versions have it as well
Yeah, I never heard much press on it, was pleasantly surprised when I saw it there ha
Eleven years after I wrote this, it gets an upvote, yay
1

You can use WinMerge to some extent to find out if the lines are found elsewhere when comparing two generated scripts. I think it works in the simpler cases.

Using WinMerge v2.12.4.0 Unicode. Note the color usage for highlighting these below.

Here is the help for the Edit -> Options -> Compare "Enable moved block detection":

3.6. Enable moved block detection Disabled (default): WinMerge does not detect when differences are due to moved lines.

Enabled: WinMerge tries to detect lines that are moved (in different locations in each file). Moved blocks are indicated by the Moved and Selected Moved difference colors. If the Location bar is displayed, corresponding difference locations in the left and right location bars are connected with a line. Showing moved blocks can make it easier to visualize changes in files, if there are not too many.

For an example, see the Location pane description in Comparing and merging files.

Improve this answer

Comments

0

I had a similar issue. My database is SQL Server 2008. I realized that, if I generate scripts of through object explorer details, then I get that order that I are viewing names. In this way I was able to compare 2 databases and findout their differences.

The only problem with this is that I had to take out separate scripts for tables/ stored procedures, triggers etc.

But we can compare easily.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.