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:
select name
from Catsic_Compare0803DiLong_2017080311.dbo.syscolumns
where id=(
select id from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects where name='t_cbjzc'
)
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:
select * from (
select name
from Catsic_Compare0803DiLong_2017080311.dbo.syscolumns
where id=(
select id from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects
where name='t_cbjzc')
) T1 FULL OUTER JOIN(
select name from Catsicgl_43_2016Eroad_2017111110.dbo.syscolumns
where id=(
select id from Catsicgl_43_2016Eroad_2017111110.dbo.sysobjects
where name='t_cbjzc'
)
) T2 on T1.name=T2.name
只显示字段字段名有差异的字段,增加一个条件即可where T1.name is null or T2.name is null
即全部code:
select * from (
select name
from Catsic_Compare0803DiLong_2017080311.dbo.syscolumns
where id=(
select id from Catsic_Compare0803DiLong_2017080311.dbo.sysobjects
where name='t_cbjzc')
) T1 FULL OUTER JOIN(
select name from Catsicgl_43_2016Eroad_2017111110.dbo.syscolumns
where id=(
select id from Catsicgl_43_2016Eroad_2017111110.dbo.sysobjects
where name='t_cbjzc'
)
) T2 on T1.name=T2.name
where T1.name is null or T2.name is null
https://www.cnblogs.com/dz-boss/p/7826477.html