How to compare same DB for their Schema and Tables?

Question

I am using SQL SERVER 2008 R2, I have two same DB,

1. ABC (with only structure like schemas, tables but no data in the table)
2. ABC1 (with schemas, tables and data in the tables)

How can I compare #1 & #2 for their Schema and Tables, do we have any software for it or we can do it in the SSMS itself.

Answer 1

You can write a sproc which will do this for you.

1.Lets get all the tables from ABC into a table variable or temp table.
2.Loop through the temp or table variable and get each table at a time.
3.Get all the column names,datatypes etc for this table and similarly get the same details for the same table from ABC1 database.
4.Also create one final result table where you just update the status against each table as match or nomatch
5.Repeat this for all the tables in the table variable.

Answer 2

You can use below software for compare database schema :

1. Redgate SQL Compare
2. EMS DB Comparer for SQL Server
3. Visual Studio Compare Database Schema

Redgate SQL Compare was more user friendly than other.

Answer 3

You can also try ApexSQL Diff - a SQL Server database comparison and synchronization tool which detects differences between database objects. It generates comprehensive reports on the found differences and can automate the synchronization process between live and versioned databases, backups, snapshots and script folders

Video - Introduction to ApexSQL Diff

Disclaimer: I work for ApexSQL as a Support Engineer