如何确定哪个列的数据类型不匹配

Question

我有一个union查询，包含了100多列，如下：

select * from table1
union
select * from table2

错误信息是：Error converting data type varchar to numeric.

怎么确定具体是哪些咧的数据类型不匹配呢？

Answer 1

试试这个查询：

SELECT IST1.COLUMN_NAME as Table1_column
      ,IST1.DATA_TYPE as Table1_datatype
	  ,IST2.COLUMN_NAME as Table2_column
	  ,IST2.DATA_TYPE as Table2_datatype
FROM information_schema.columns IST1 INNER JOIN information_schema.columns IST2 
  ON IST1.ORDINAL_POSITION=IST2.ORDINAL_POSITION
WHERE IST1.table_name='table1' AND IST1.TABLE_SCHEMA='dbo'
  AND IST2.table_name='table2' AND IST2.TABLE_SCHEMA='dbo'
  AND IST1.DATA_TYPE <> IST2.DATA_TYPE

---

如果答案是正确的解决方案，请点击“接受答案”并投赞成票。如果您对此答案有其他疑问，请点击“评论”。