Post reply

Script to compare schema across databases?

  • January 12, 2016 at 9:06 am

    #303632

    Hey all,

    I know that there are tools out there which allow for schema comparison (SQL Compare for example). What I'm looking for is a bit more low-level.

    I'd like a script that I can run on a daily basis, which will just alert me to differences in schema between two databases. I'd imagine that there are probably system-level tables which will provide me with this information. Can someone point me towards which tables these would be, so I can create the script? Or alternatively if there are scripts already available, to point me to those?

    I'd imagine that sys.tables / sys.columns would be the one I would look at for table comparison, and sys.procedures / sys.sql_modules for the stored procedures. Are there any others I should be looking at? Which ones store functions?

  • January 12, 2016 at 10:12 am

    #1851349

    Some third-party vendors offer such tools. Naturally all -- or at least most -- of them cost money.

    MS provides the tablediff utility, which is free. It's command-line only, but it does work.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • January 12, 2016 at 11:43 am

    #1851378

    sys.all_sql_modules stores all sql modules, comparing tables it a bit more of a task, strongly suggest using 3rd party tool such as SQL Compare for this. A option could be using SMO but the task can become complicated very quickly.

    😎

  • January 12, 2016 at 11:55 am

    #1851385

    Are you going to compare indexes and constraints as well? Do you care about differences in names for system named objects?

    What about implementing some monitoring using DDL triggers?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 12, 2016 at 11:55 am

    #1851386

    Microsoft SQL Server Data Tools (SSDT) is a free download, and it has features for schema comparison, data comparison, and scripting DDL/DML for the differences.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 12, 2016 at 2:40 pm

    #1851433

    The reason there are tools around this task is because there really isn't any simple, quick & easy way to get it done. Especially not one that supports all the various types of objects, dependencies, etc.

    DISCLOSURE: I work for Redgate

    I've published several articles on Simple-Talk around automating SQL Compare to do exactly what you're looking for.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 6 posts - 1 through 5 (of 5 total)

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