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 #table2set @tabella1 =’ln.vFactTransazioniFInanziarieFInalizzate’
set @tabella2 =’sto.vFactTransazioniFinanziarie’
–drop table #table1select –s.name+’.’+t.name as nometabella
c.name as Columnname
,ty.name as ColumnType
,c.max_length
into #table1
from sys.tables tleft join sys.all_columns c
on t.object_id = c.object_idleft join sys.types ty
on ty.user_type_id = c.user_type_idleft join sys.schemas s
on s.schema_id = t.schema_idwhere 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 tleft join sys.all_columns c
on t.object_id = c.object_idleft join sys.types ty
on ty.user_type_id = c.user_type_idleft join sys.schemas s
on s.schema_id = t.schema_idwhere s.name+’.’+t.name = @tabella2
select
t1.Columnname
,t1.ColumnType
,t1.max_length
,t2.ColumnType
,t2.max_length
from #table1 t1left join #table2 t2
on t1.Columnname = t2.Columnnamewhere 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 #table2set @tabella1 =’ln.vFactTransazioniFInanziarieFInalizzate’
set @tabella2 =’sto.vFactTransazioniFinanziarie’
–drop table #table1select –s.name+’.’+t.name as nometabella
c.name as Columnname
,ty.name as ColumnType
,c.max_length
into #table1
from sys.views tleft join sys.all_columns c
on t.object_id = c.object_idleft join sys.types ty
on ty.user_type_id = c.user_type_idleft join sys.schemas s
on s.schema_id = t.schema_idwhere 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 tleft join sys.all_columns c
on t.object_id = c.object_idleft join sys.types ty
on ty.user_type_id = c.user_type_idleft join sys.schemas s
on s.schema_id = t.schema_idwhere s.name+’.’+t.name = @tabella2
select
t1.Columnname
,t1.ColumnType
,t1.max_length
,t2.ColumnType
,t2.max_length
from #table1 t1left join #table2 t2
on t1.Columnname = t2.Columnnamewhere 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.