Post reply

schema differences

  • November 30, 2007 at 3:03 pm

    #184515

    Is there a simple way to determine differences in table schemas between 2 databases? I tried tablediff.exe but that seems aimed at data differences, not schema differences. I have 60 or so tables to test so it would be nice to not have to do them one by one.

  • December 2, 2007 at 5:58 pm

    #756347

    Hi stef,

    Use a trial of SQL Compare from redGate (a good tool).

    Regards,

    Ahmed

  • December 2, 2007 at 10:36 pm

    #756361

    Hi !

    You can also use apex SQL trial verison for comparing schema of objects betwn 2 DBs.

  • December 3, 2007 at 1:46 am

    #756381

    The cheapest way - script both whole databases or selected objectswith the same scripting options and use WinDiff which is generally lurking somewhere on Microsoft systems e.g. Program Files\Microsoft Visual Studio 8\Common7\Tools\Bin directory. Use save to ANSI files not unicode!

    I've use this for years to compare development, test and production versions of databases to check exactly what needs to be updated for a release.

  • December 3, 2007 at 6:26 am

    #756443

    Another cheap way is to script them & then compare using Microsoft Word - "Tools ~ Compare and Merge Documents..." which applies blue & red colored text to denote the differences.

  • December 3, 2007 at 10:29 am

    #756574

    All great suggestions, thanks. I ended up using Windiff and it worked fine.

  • December 4, 2007 at 4:57 am

    #756861

    FWIW, I use StarInix' free database comparison tool. Very useful.

    http://www.starinix.com/sqlcompare02.htm

    Derek

  • December 4, 2007 at 5:18 am

    #756869

    With DB Side-By-Side (http://www.schematodoc.com) you create xml snapshots of your database's metadata. You can then run any two snapshots through the program's comparison utility and quickly identify differences in a table's primary fields, fields (type, size, defaults, nullable), indexes, check constraints, foreign key constraints, views, and stored procedures. You can compare any two databases, or the same database at two difference points in time.

  • December 4, 2007 at 3:34 pm

    #757153

    Stef,

    Because you say "simple", I'll offer this: Sometimes I only want to compare columns, not triggers, SP's or anything else. I select * from INFORMATION_SCHEMA.COLUMNS into a temp table for each database I want to compare.

    Then I query those two temp tables using a full outer join on table_name and column_name, looking for missing columns. Likewise, you can compare the type and size. I mange over 60 databases across 20 servers so this method works well for me. But for a full compare of all objects, use Red Gate, but it's still time-consuming.

    In theory, you could use the INFORMATION_SCHEMA to compare all objects. Hope this helped.

  • December 4, 2007 at 8:22 pm

    #757202

    Yes, great idea- I'll try that too, thanks!

  • December 6, 2007 at 11:25 am

    #757797

    Hi,

    I want to share the info, there is new tool in SQL Server 2005 Called tablediff.exe located in "\Program Files\Microsoft SQL Server\90\COM\tablediff.exe".

    Check the following link for more info http://msdn2.microsoft.com/en-us/library/ms162843.aspx

    There is also a GUI version developped by "Mladen" check the link http://weblogs.sqlteam.com/mladenp/archive/2007/08/10/60279.aspx

    Regards,

    Ahmed

  • October 20, 2008 at 4:46 pm

    #887361

    hi there, do you know if you can use tablediff but to compare data from an sql server 7.0 with a 2k5? is that possible to be accomplished with this tool? Thanks

  • October 21, 2008 at 9:40 am

    #887682

    How about this ?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply