SQL Server比较2table字段的差异
由于项目前后用了2个数据库，需要统计数据库结构的变化，需要统计每个表的变化，由于人工核对挺浪费时间，就写了一点代码：
1.统计表的字段数量（查询表有多少列）：
select count(name) from syscolumns where id=object_id('表名')
eg:select count(name) from syscolumns where id=object_id('t_dk')
2.查询数据库字段名 （表有哪些字段）
select name
from 数据库名.dbo.syscolumns
where id=(
select id from 数据库名.dbo.sysobjects where name='表名'
)
eg:
1 select name 2 3 from Catsic_Compare0803DiLong_2017080311.dbo.syscolumns 4 5 where id=( 6 7 select id from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects where name='t_cbjzc' 8 9 )
3.比较两个数据库相应表的差异（查询表对应的字段是否一致）
本部分是基于2写的：
select * from (
select name
from 数据库A.dbo.syscolumns
where id=(
select id from 数据库A.dbo.sysobjects
where name='表名A')
) T1 FULL OUTER JOIN(
select name from 数据库B.dbo.syscolumns
where id=(
select id from 数据库B.dbo.sysobjects
where name='表B'
)
) T2 on T1.name=T2.name
eg：
1 select * from ( 2 select name 3 from Catsic_Compare0803DiLong_2017080311.dbo.syscolumns 4 where id=( 5 select id from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects 6 where name='t_cbjzc') 7 ) T1 FULL OUTER JOIN( 8 select name from Catsicgl_43_2016Eroad_2017111110.dbo.syscolumns 9 where id=( 10 select id from Catsicgl_43_2016Eroad_2017111110.dbo.sysobjects 11 where name='t_cbjzc' 12 ) 13 ) T2 on T1.name=T2.name
只显示字段字段名有差异的字段，增加一个条件即可where T1.name is null or T2.name is null
即全部code：
1 select * from ( 2 select name 3 from Catsic_Compare0803DiLong_2017080311.dbo.syscolumns 4 where id=( 5 select id from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects 6 where name='t_cbjzc') 7 ) T1 FULL OUTER JOIN( 8 select name from Catsicgl_43_2016Eroad_2017111110.dbo.syscolumns 9 where id=( 10 select id from Catsicgl_43_2016Eroad_2017111110.dbo.sysobjects 11 where name='t_cbjzc' 12 ) 13 ) T2 on T1.name=T2.name 14 15 where T1.name is null or T2.name is null
