WikiDBA

Its required in our daily practices or sometimes we have to compare two or more tables to find out where tables are same in terms of column’s data types.

Below is the query to compare data types between two tables, for this, we can use the [INFORMATION_SCHEMA].[COLUMNS] system views to verify and compare the information.

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)NOTNULL,
  [Name][varchar](10)NULL,
  [Basic][numeric](18, 2)NULL,
  [Age] [int]
) ON [PRIMARY]
GO

Now to find the data type mismatched columns, use below query,

Select c1.table_name,c1.COLUMN_NAME,c1.DATA_TYPE,c2.table_name,c2.DATA_TYPE,c2.COLUMN_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c1
Left join [INFORMATION_SCHEMA].[COLUMNS] c2 on c1.COLUMN_NAME=c2.COLUMN_NAME
where c1.TABLE_NAME=’emp1′ and c2.TABLE_NAME=’emp2′ and c1.data_type<>c2.DATA_TYPE