Script to compare schema across databases?

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

  • 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 give you the shirt off *someone else's* back.

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

    😎

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

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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