January 12, 2016 at 9:06 am
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
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 12, 2016 at 11:43 am
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
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?
January 12, 2016 at 11:55 am
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
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