Post reply

Need to find schema difference between two databases

  • September 24, 2010 at 4:21 am

    #148708

    Hi all,

    I need the assistance for the following criteria. Could any one help/suggest me?

    Need to find schema difference between two databases. Need to find changes done to objects between production and development databases.

    Get the list of changes done to the database objects.

    Thanks in advance

  • September 24, 2010 at 4:35 am

    #1226824

    For jobs like this I recommend getting a copy of RedGate's SQLCompare. It'll likely cost less than the time required to write such a solution from scratch.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 24, 2010 at 4:41 am

    #1226826

    Thanks you for the suggestion. However any other alternate way like script.

  • September 24, 2010 at 4:44 am

    #1226829

    Sure, but it's terribly complex. Use the system views (sys.objects, sys.columns, sys.indexes.....) to build up queries to get the definitions and then compare the resultsets between the servers. It'll be a fairly time-consuming script to write.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 24, 2010 at 4:56 am

    #1226836

    Thanks again. I echo with your words, developing queries for the above criteria is complex and time consuming at the same time there are no permission to use the tools from my end.

    Please give me the sample script if any one worked/have on the criteria like above.

    Thanks,

  • September 24, 2010 at 5:07 am

    #1226842

    Bagath (9/24/2010)

    Please give me the sample script if any one worked/have on the criteria like above.

    I use SQLCompare when I'm doing work like that. Search the script library here, there maybe something that'll give you a start

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 24, 2010 at 8:06 am

    #1226993

    You might be able to find a free software that could help you here: http://www.supershareware.com/sql-server-comparison-free/

    I'd go for something like that rather than try to write it myself.

    Do you have Visual Studio Team System? If so, there's a schema comparison tool there. It's not as good as Red Gate's, but it'll work.

    You might try this one;

    http://xsqlsoftware.com/

    ----------------------------------------------------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

  • September 24, 2010 at 10:16 pm

    #1227318

    Thanks you.

    I will try to find/write out the tool/script.

    Once again thanks to all for the valuable suggestions and help.

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

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