Often using SQL we need to compare the data types of the columns of two different tables. For example, when we bring the data from the Stage to the data warehouse, or if we have to put into union two tables with similar data.

When this happens, more often in error or distraction it appears there the usual generic error in the case in which the types of the two data tables are incorrect.

is very difficult to identify where the error is because sql does not show the column, I wrote this simple script that allows you to identify the error:

declare @Tabella1 varchar(100)
declare @tabella2 varchar(100)

IF OBJECT_ID(‘tempdb..#table1’) IS NOT NULL drop table #table1
IF OBJECT_ID(‘tempdb..#table2’) IS NOT NULL drop table #table2

set @tabella1 =’ln.vFactTransazioniFInanziarieFInalizzate’
set @tabella2 =’sto.vFactTransazioniFinanziarie’
–drop table #table1

select –s.name+’.’+t.name as nometabella
c.name as Columnname
,ty.name as ColumnType
,c.max_length
into #table1
from sys.tables t

left join sys.all_columns c
on t.object_id = c.object_id

left join sys.types ty
on ty.user_type_id = c.user_type_id

left join sys.schemas s
on s.schema_id = t.schema_id

where s.name+’.’+t.name = @tabella1
select –s.name+’.’+t.name as nometabella
c.name as Columnname
,ty.name as ColumnType
,c.max_length
into #table2
from sys.views t

left join sys.all_columns c
on t.object_id = c.object_id

left join sys.types ty
on ty.user_type_id = c.user_type_id

left join sys.schemas s
on s.schema_id = t.schema_id

where s.name+’.’+t.name = @tabella2

select
t1.Columnname
,t1.ColumnType
,t1.max_length
,t2.ColumnType
,t2.max_length
from #table1 t1

left join #table2 t2
on t1.Columnname = t2.Columnname

where t2.ColumnType <> t1.ColumnType

You just need to change the values in the two variables @Tabella1 and @Tabella2 (marked in bold inside the script). You must write both the name of the table and his schema.

If you want to do the same thing on the views the script is similar:

declare @Tabella1 varchar(100)
declare @tabella2 varchar(100)

IF OBJECT_ID(‘tempdb..#table1’) IS NOT NULL drop table #table1
IF OBJECT_ID(‘tempdb..#table2′) IS NOT NULL drop table #table2

set @tabella1 =’ln.vFactTransazioniFInanziarieFInalizzate’
set @tabella2 =’sto.vFactTransazioniFinanziarie’
–drop table #table1

select –s.name+’.’+t.name as nometabella
c.name as Columnname
,ty.name as ColumnType
,c.max_length
into #table1
from sys.views t

left join sys.all_columns c
on t.object_id = c.object_id

left join sys.types ty
on ty.user_type_id = c.user_type_id

left join sys.schemas s
on s.schema_id = t.schema_id

where s.name+’.’+t.name = @tabella1
select –s.name+’.’+t.name as nometabella
c.name as Columnname
,ty.name as ColumnType
,c.max_length
into #table2
from sys.views t

left join sys.all_columns c
on t.object_id = c.object_id

left join sys.types ty
on ty.user_type_id = c.user_type_id

left join sys.schemas s
on s.schema_id = t.schema_id

where s.name+’.’+t.name = @tabella2

select
t1.Columnname
,t1.ColumnType
,t1.max_length
,t2.ColumnType
,t2.max_length
from #table1 t1

left join #table2 t2
on t1.Columnname = t2.Columnname

where t2.ColumnType <> t1.ColumnType

You just need to change the values in the two variables @Tabella1 and @Tabella2 (marked in bold inside the script). You must write both the name of the table and his schema.