• 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

     

  • 相关阅读:
    在Windows 10 64位上编译DCMTK
    python递归解决汉诺塔
    python迭代和递归实现斐波那契
    20199302 2019-2020-2 《网络攻防实践》第4周作业
    ssh爆破
    20199302 2019-2020-2 《网络攻防实践》第3周作业
    字符串模版替换的方法MessageFormat.format(String pattern, Object ... arguments)
    Java并发编程之LinkedBlockingDeque阻塞队列详解
    理解Spring容器、BeanFactory和ApplicationContext
    Steam之两个list间交集、并集、差集
  • 原文地址:https://www.cnblogs.com/dz-boss/p/7826477.html
Copyright © 2020-2023  润新知