0

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.

Ocaso Protal
  • 19,362
  • 8
  • 76
  • 83
Vikrant More
  • 5,182
  • 23
  • 58
  • 90

3 Answers3

4

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.

mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
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.
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
  • can you please guide or help me to write – Vikrant More Aug 28 '12 at 09:15
  • ok.do you have these dbs on same server or on different servers? – AnandPhadke Aug 28 '12 at 09:17
  • its on same server i.e. on (local) – Vikrant More Aug 28 '12 at 09:18
  • select name from sys.objects where type='U' – AnandPhadke Aug 28 '12 at 09:22
  • i have done the code but how can i generate the script for missing table which is exist in DB ABC1 but not in ABC so i wanted to update ABC (old DB) to compare the next build DB with the ABC. – Vikrant More Aug 28 '12 at 10:24
  • 1
    you mean you want to create the table which is there in ABC1 and not in ABC? – AnandPhadke Aug 28 '12 at 10:55
  • select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tablename' – AnandPhadke Aug 28 '12 at 11:03
  • use this query to get the structure of the table from ABC1 db and put this result into table variable and then from the table variable just create the create table script dynamically and then execute that script at DB ABC – AnandPhadke Aug 28 '12 at 11:04
  • Hmmm, it is nor just like that... writing update scripts that will synchronize two databases manually is quite a risky job. You can maybe try to compare by creating scripts for all objects and then use a file compare tool to find the deltas between the two schemas and change it. But for even a bit larger databases I would not recommend this method since it’s quite easy to miss some change. Every mistake may lead to massive loss of data... select the wrong column to delete, or change your varchar(20) into a varchar(2) and you are lost. –  Oct 18 '13 at 21:02
0

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

Milica Medic Kiralj
  • 3,580
  • 31
  • 31