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

Advertisements
Comments
  1. kiran says:

    Thank You

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s