Post reply

how do we caompare the table structure in 2 databases

  • February 25, 2010 at 9:41 am

    #221033

    hi all,

    We have 2 environment, and some chnages were made on DEV for a tbalw with large no of columns,

    now we need to compare what columns got added to that tbale, so that we can alter the table in QA.

    basically teh name of tbale is same,but structure changed in dev over time.

    Please direct me on the right path...

    thanks

    s

  • February 25, 2010 at 10:08 am

    #1125090

    The best way to do this is through a tool. I believe Redgate has something that does this.

    If you just want a list of columns that are not common between the two tables, you could use something like this:

    select name from QADB.sys.columns where OBJECT_NAME(object_id) = 'I014A'

    except

    select name from DEVDB.sys.columns where OBJECT_NAME(object_id) = 'I014A'

    Converting oxygen into carbon dioxide, since 1955.

  • February 25, 2010 at 10:13 am

    #1125092

    Redgate SQL Compare

  • February 25, 2010 at 11:01 am

    #1125118

    RedGate SQL Compare is a useful utility for this.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 25, 2010 at 11:12 am

    #1125124

    Another vote for Red Gate SQL Compare. The price on the Red Gate tools is entirely reasonable. If you're working for a company of any substantial size, you can justify the purchase quite easily.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 25, 2010 at 11:23 am

    #1125132

    Red Gate SQL Compare is definitely the simplest, easiest option.

    But if that is not workable for you look into using tablediff.

  • February 25, 2010 at 3:29 pm

    #1125261

    And another vote for Redgate's SQL compare.

    Gethyn Elliswww.gethynellis.com

  • February 25, 2010 at 3:36 pm

    #1125263

    I work for Red Gate, but their tool or any other compare will do this very inexpensively. It sounds like a decent sized purchase for your boss, but consider your time and that fact that you will likely do this multiples times as you try and deploy changes. Making mistakes can easily exceed the cost of the tool, as can manually going through and checking things.

    These tools are also very well tested, something that isn't true of a homegrown tool.

    If you deploy changes, make the investment in the tools. Red Gate's or another one.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

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

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