Post reply

Fast way to compare the fields between two databases

  • January 5, 2009 at 2:06 pm

    #383681

    Hi everyone,

    I have a question concerning the a fast way to compare the fields between two databases. Let's say two databases are pretty similar--they have the same tables, for the most part. However, there are small differences between the fields of the tables. Some fields are named slightly differently, some fields may be in one database that aren't in the other, and some fields with the same name may have two different types in the different databases. As a result, some software isn't interfacing properly with one of the databases.

    I'm looking for a fast, somewhat automated way to identify the differences between the two databases. It'd be nice to have a list of fields that are in database A, but not database B, vice versa, and a list of fields whose types differ between the two databases. Does anyone know of a utility that does such a thing?

    If there isn't one available, I was thinking of coming up with some way of using Excel to compare the names of the fields. In this case, my question would be: what is the best way to get the list of fields (and their types) of all the tables in a database in an easily parsible text file? I was thinking I could mainly use the Script to --> Insert option, but I was hoping that there was a better way.

    Any advice anyone could give concerning these issues would be greatly appreciated.

  • January 5, 2009 at 2:16 pm

    #921431

    I use Redgates SQL Compare to perform these kinds of comparisons. There are other tools available that do the same thing, some are even free.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 5, 2009 at 2:37 pm

    #921445

    I also use RedGate SQLCompare, but if the databases are on the same server you can write a query using the information_schemas views and get a rough comparison of column names, types, etc...

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 5, 2009 at 5:10 pm

    #921507

    Try this:

    Select * From TargetDB.INFORMATION_SCHEMA.COLUMNS

    EXCEPT

    Select * From ReferenceDB.INFORMATION_SCHEMA.COLUMNS

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • January 5, 2009 at 9:07 pm

    #921554

    Here is another quick and dirty way to compare if both databases are on the same server. You probably want to take a look at information_schema.columns and add a few more things like ordinal position, is nullable, etc.

    SELECT

    DB1.TABLE_CATALOG AS DB1

    ,DB2.TABLE_CATALOG AS DB2

    -- table and column names

    ,COALESCE(DB1.TABLE_NAME, DB2.TABLE_NAME) AS TABLE_NAME

    ,COALESCE(DB2.COLUMN_NAME, DB1.COLUMN_NAME) AS COLUMN_NAME

    -- any other column attributes you want to list/compare

    ,DB1.DATA_TYPE AS DB1_DATA_TYPE

    ,DB2.DATA_TYPE AS DB2_DATA_TYPE

    ,CASE WHEN DB1.DATA_TYPE = DB2.DATA_TYPE THEN 1 ELSE 0 END AS Match_Fl

    FROM

    (

    SELECT

    TABLE_CATALOG

    ,TABLE_SCHEMA

    ,TABLE_NAME

    ,COLUMN_NAME

    ,DATA_TYPE

    FROM

    DB1.information_schema.columns -- change to name of actual db

    ) DB1

    FULL OUTER JOIN

    (

    SELECT

    TABLE_CATALOG

    ,TABLE_SCHEMA

    ,TABLE_NAME

    ,COLUMN_NAME

    ,DATA_TYPE

    FROM

    DB2.information_schema.columns -- change to name of actual db

    ) DB2

    ON DB1.TABLE_NAME = DB2.TABLE_NAME

    AND DB1.COLUMN_NAME = DB2.COLUMN_NAME

    ORDER BY

    3,4

  • January 5, 2009 at 9:21 pm

    #921555

    The attached script gives a little more column level detail. If you are comparing a prod database to a dev or QA database you could run the query in each one then copy the results to separate excel worksheets.

    Then on the prod and a QA excel worksheets concatenate all of the columns together and use the countif() excel function to see if the concatenated values exist on both worksheets. If it’s not an exact match countif() will return a zero and you can probably eyeball the differences from there.

    Hope that helps.

  • January 6, 2009 at 11:33 am

    #922000

    I'm going to add a vote for "buy something that does this for you". RedGate's Compare is good. I use ApexSQL Diff, because it compares both structure and data in a single product. There might be others.

    Get a free trial, then buy the one you like best. Don't even try to reinvent the wheel on this one.

    They will also come in very, very handy when you want to synchronize Dev with Test or Test with QA or QA with Prod, and so on. They pay for themselves very rapidly because of things like that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 6, 2010 at 1:58 pm

    #1146771

    Short, Sweet and to the point !! THANKS !! 😀

Viewing 8 posts - 1 through 7 (of 7 total)

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