Writing a Query for adding missing columns in tables in one database from another similar database

Question

We have 2 databases: QMS and QMS_Chennai.

QMS contains these tables:

tbl_table1
tbl_table2
tbl_table3

QMS_Chennai contains these tables:

tbl_table1
tbl_table2

The tables in the QMS database contain a few columns which are not in the same tables in the QMS_Chennai database.

I need to check what tables are present in both databases and add the additional columns to tables in the QMS_Chennai database from QMS.

Need to write some query as we have to face this scenario number of times in case of different databases. We are using SQL Server 2008 R2

Thanks

Abhishek · Accepted Answer · 2015-08-22 08:20:22Z

2

Schmea Compare from VS 2010 will help you in this. Link for more details. Schema Compare

For higher version of VS i.e VS 2012 check this Schema Compare

or for Vs 2013 this Schema Compare

answered Aug 22, 2015 at 8:20

Abhishek

2,4901 gold badge24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

david sam · Accepted Answer · 2015-08-22 09:36:01Z

0

you can get the table name which are present in both table by this query:-

  select * from QMS .INFORMATION_SCHEMA.TABLES as bb 
  inner join QMS_Chennai .INFORMATION_SCHEMA.TABLES as cc
  on bb.TABLE_NAME=cc.TABLE_NAME

answered Aug 22, 2015 at 9:36

david sam

5312 gold badges8 silver badges25 bronze badges