查询两个表的字段属性差异的sql语句

我们经常在测试库表中 插入数据没问题，但是在正式库同一张表中插入数据就会有问题

这种情况常常是因为 某一张表 修改了 字段属性之后 没有对应的修改 另一张表的属性 

那我们怎么能找出那些有区别的 字段属性呢 

架设 tableTest是测试库表   tableUse是正式库的表

通过下面的语句即可查询出 有差异的字段属性

declare @tableA nvarchar(127),@tableB nvarchar(127)
declare @idA int,@idB int


SET @tableA = 'tableTest'
SET @tableB = 'tableUse'


select @idA = id from sysobjects where name = @tableA
select @idB = id from sysobjects where name = @tableB
 
select b.name as 表名,a.name as 字段名,a.length as 长度,c.NAME as 类型
into #tmpTable1
from syscolumns a
inner join sysobjects b on a.id = b.id
inner join SYSTYPES c on a.XUSERTYPE = c.XUSERTYPE
where  b.XTYPE = 'U' and b.id = @idA


select b.name as 表名,a.name as 字段名,a.length as 长度,c.NAME as 类型
into #tmpTable2
from syscolumns a
inner join sysobjects b on a.id = b.id
inner join SYSTYPES c on a.XUSERTYPE = c.XUSERTYPE
where  b.XTYPE = 'U' and b.id = @idB


select @tableA as 表A,@tableB as 表B,a.字段名,a.长度 as 表A长度,b.长度 as 表B长度,a.类型 as 表A类型,b.类型 as 表B类型
from #tmpTable1 a 
inner join #tmpTable2 b on a.字段名 = b.字段名 and (a.长度 <> b.长度 or a.类型 <> b.类型)
drop table #tmpTable1
drop table #tmpTable2