Posts Tagged ‘column mismatch in 2 tables’

Compare the extra Columns between Tables

Posted: October 25, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , ,
0

Sometimes its needed to check where two tables having the same number of columns?

To demonstrate this first create two tables as EMP1 and EMP2.

Lets we have two Tables as EMP1 and EMP2 as below,

Suppose table EMP1 is as below

USE [VirendraTest]

GO

CREATE TABLE [dbo].[Emp1]
( [ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NULL,
[Basic] [numeric](18, 2) NULL
   ) ON [PRIMARY]

GO

and table EMP2 is as below,

USE [VirendraTest]

GO

CREATE TABLE [dbo].[Emp2]
( [ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](10) NULL,
[Basic] [numeric](18, 2) NULL,
[Age] [int]
   ) ON [PRIMARY]
 GO

Now to find the extra columns from EMP2 to EMP1 , we can use below query,

Select c2.table_name,c2.COLUMN_NAME from [INFORMATION_SCHEMA].[COLUMNS] c2
where table_name=‘Emp2’ and c2.COLUMN_NAME not in (select column_name from [INFORMATION_SCHEMA].[COLUMNS] where table_name=’emp1′)