Last post Jan 20, 2018 06:11 AM by Deepak Panchal
Jan 10, 2018 06:29 PM|inkaln|LINK
I'm looking to see if there's a built in way in sql server to write a script that I want run each night to make sure that data schema including tables, stored procedures, functions, etc are the same. I have first database that will be the up to date one
and I want to compare several other databases to match the design of the first one with the exception of certain stored procedures that start with a certain name to be excluded in comparison. What's the best way to accomplish this? THanks.
Jan 10, 2018 07:09 PM|PatriceSc|LINK
I believe you should be able to use
You also have a schema / data comparison tool in VS 2017 Community.
Edit: try perhaps also https://blogs.msdn.microsoft.com/ssdt/2016/09/20/schema-compare-in-sqlpackage-and-the-data-tier-application-framework-dacfx/
Jan 11, 2018 02:23 AM|Deepak Panchal|LINK
you had mentioned that you want to compare two database.
you can try to use SSDT.
SQL Server Data Tools (SSDT) includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot or
.dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete you can update the target directly (if the target is a project or a database) or generate
an update script that has the same effect.
The differences between source and target appear in a grid for easy review. You can drill into and review each difference in the results grid or in script form. You can then selectively exclude specific differences.
You can save comparisons either as part of a SQL Server Database project or as a standalone file. You can also set options that control the scope of the comparison and aspects of the update. Then you can save the comparison so that you can easily
repeat the same comparison later or use it as the starting point for new comparison.
How to: Use Schema Compare to Compare Different Database Definitions
if you want to compare Tables and Data then below links may help you.
Compare two databases for objects differences
Ways to compare and find differences for SQL Server tables and data
Jan 11, 2018 06:43 PM|inkaln|LINK
I have used it in visual studio but have to select each target to compare with the source db....I am looking into automating with possible the scmp file from sources db but program a script where I compare several target DBs with the script file, excluding
some of the stored procedures and objects. I'm guessing this is doable in visual studio. Thanks for the information above.
Jan 20, 2018 06:11 AM|Deepak Panchal|LINK
Did you try the script that mentioned in the link which i posted in my previous reply?
If that not work for you then you may need to try some third party tools which can fulfil your requirement.
Compare SQL Server database schemas automatically
SQL Server Database Comparison Tool
Let us know about current status of your issue.