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

posted on 2019-09-17 17:42  扶强  阅读(483)  评论(0编辑  收藏  举报