转:邹建--比较两个数据库的表结构差异2

 

转:http://blog.csdn.net/zjcxc/archive/2004/01/04/20088.aspx

转:http://topic.csdn.net/t/20031113/17/2457717.html

 

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[p_comparestructure] ' )  and   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 )
drop   procedure   [ dbo ] . [ p_comparestructure ]
GO



CREATE   proc    p_comparestructure   
   @dbname1     varchar ( 250 ),  -- 要比较的数据库名1   
   @dbname2     varchar ( 250 )  -- 要比较的数据库名2   
   as    
   create     table    #tb1(表名1    varchar ( 250 ),字段名    varchar ( 250 ),序号    int ,标识    bit ,主键    bit ,类型    varchar ( 250 ),   
  占用字节数    int ,长度    int ,小数位数    int ,允许空    bit ,默认值    nvarchar ( 2000 ), [ 字段说明 ]    sql_variant)   
    
   create     table    #tb2(表名2    varchar ( 250 ),字段名    varchar ( 250 ),序号    int ,标识    bit ,主键    bit ,类型    varchar ( 250 ),   
  占用字节数    int ,长度    int ,小数位数    int ,允许空    bit ,默认值    nvarchar ( 2000 ), [ 字段说明 ]    sql_variant)   
    
   -- 得到数据库1的结构   
   exec ( ' insert   into   #tb1   SELECT     
  表名=d.name,字段名=a.name,序号=a.colid,   
  标识=case   when   a.status=0x80   then   1   else   0   end,
  主键=Case When (Exists(select 1
        from  ' +   @dbname1   +   ' ..sysindexes i
        join  ' +   @dbname1   +   ' ..sysindexkeys k on i.id = k.id and i.indid = k.indid
        join  ' +   @dbname1   +   ' ..sysobjects o on i.id = o.id
        join  ' +   @dbname1   +   ' ..syscolumns c on i.id=c.id and k.colid = c.colid
        join  ' +   @dbname1   +   ' ..systypes t on c.xusertype=t.xusertype
        where o.xtype =  '' U ''  and o.name=d.name and c.name = a.name
        and exists(select 1 from  ' +   @dbname1   +   ' ..sysobjects where xtype =  '' PK ''  and parent_obj=i.id and name = i.name))
)   then   1   else   0   end,   
  类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,   
  默认值=isnull(e.text, '''' ),字段说明=isnull(g.value,  '''' )
  FROM    ' + @dbname1 + ' ..syscolumns   a   
  left   join    ' + @dbname1 + ' ..systypes   b   on   a.xtype=b.xusertype   
  inner   join    ' + @dbname1 + ' ..sysobjects   d   on   a.id=d.id     and   d.xtype= '' U ''    and     d.name<> '' dtproperties ''    
  left   join    ' + @dbname1 + ' ..syscomments   e   on   a.cdefault=e.id   
  left   join    ' + @dbname1 + ' ..sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid       
  order   by   a.id,a.colorder ' )   
    
   -- 得到数据库2的结构   
   exec ( ' insert   into   #tb2   SELECT     
  表名=d.name,字段名=a.name,序号=a.colid,   
  标识=case   when   a.status=0x80   then   1   else   0   end,   
  主键=Case When (Exists(select 1
        from  ' +   @dbname2   +   ' ..sysindexes i
        join  ' +   @dbname2   +   ' ..sysindexkeys k on i.id = k.id and i.indid = k.indid
        join  ' +   @dbname2   +   ' ..sysobjects o on i.id = o.id
        join  ' +   @dbname2   +   ' ..syscolumns c on i.id=c.id and k.colid = c.colid
        join  ' +   @dbname2   +   ' ..systypes t on c.xusertype=t.xusertype
        where o.xtype =  '' U ''  and o.name=d.name and c.name = a.name
        and exists(select 1 from  ' +   @dbname2   +   ' ..sysobjects where xtype =  '' PK ''  and parent_obj=i.id and name = i.name))
)   then   1   else   0   end,   
  类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,   
  默认值=isnull(e.text, '''' ),字段说明=isnull(g.value,  '''' )  
  FROM    ' + @dbname2 + ' ..syscolumns   a   
  left   join    ' + @dbname2 + ' ..systypes   b   on   a.xtype=b.xusertype   
  inner   join    ' + @dbname2 + ' ..sysobjects   d   on   a.id=d.id     and   d.xtype= '' U ''    and     d.name<> '' dtproperties ''    
  left   join    ' + @dbname2 + ' ..syscomments   e   on   a.cdefault=e.id   
  left   join    ' + @dbname2 + ' ..sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid       
  order   by   a.id,a.colorder ' )   
   -- and   not   exists(select   1   from   #tb2   where   表名2=a.表名1)   
select  比较结果 = case   when  a.表名1  is   null   and  b.序号 = 1    and  b.序号 = 1   and   not   exists ( select   1   from  #tb1  where  表名1 = b.表名2)  then   ' 库 ' +   @dbname1   +   ' 缺少表: ' + b.表名2
   when  b.表名2  is   null   and  a.序号 = 1   and   not   exists ( select   1   from  #tb2  where  表名2 = a.表名1)  then   ' 库 ' +   @dbname2   +   ' 缺少表: ' + a.表名1
   when  a.字段名  is   null   and   exists ( select   1   from  #tb1  where  表名1 = b.表名2)  then   ' 库 ' +   @dbname1   +   ' 表[ ' + b.表名2 + ' ]缺少字段： ' + b.字段名
   when  b.字段名  is   null   and   exists ( select   1   from  #tb2  where  表名2 = a.表名1)  then   ' 库 ' +   @dbname2   +   ' 表[ ' + a.表名1 + ' ]缺少字段： ' + a.字段名
   when  a.标识 <> b.标识  then   ' 标识不同 '
   when  a.主键 <> b.主键  then   ' 主键设置不同 '
   when  a.类型 <> b.类型  then   ' 字段类型不同 '
   when  a.占用字节数 <> b.占用字节数  then   ' 占用字节数 '
   when  a.长度 <> b.长度  then   ' 长度不同 '
   when  a.小数位数 <> b.小数位数  then   ' 小数位数不同 '
   when  a.允许空 <> b.允许空  then   ' 是否允许空不同 '
   when  a.默认值 <> b.默认值  then   ' 默认值不同 '
   when  a.字段说明 <> b.字段说明  then   ' 字段说明不同 '
  else   ''   end ,
  *
  
   from    #tb1   a   
   full     join    #tb2   b    on    a.表名1 = b.表名2    and    a.字段名 = b.字段名   
   where    a.表名1    is     null     or    a.字段名    is     null     or    b.表名2    is     null     or    b.字段名    is     null      
   or    a.标识 <> b.标识    or    a.主键 <> b.主键    or    a.类型 <> b.类型   
   or    a.占用字节数 <> b.占用字节数    or    a.长度 <> b.长度    or    a.小数位数 <> b.小数位数   
   or    a.允许空 <> b.允许空    or    a.默认值 <> b.默认值    or    a.字段说明 <> b.字段说明   
   order     by     isnull (a.表名1,b.表名2), isnull (a.序号,b.序号) -- isnull(a.字段名,b.字段名)   

-- SELECT * FROM #tb1 WHERE 表名1 = 'O_OrderItems'
-- SELECT * FROM #tb2 WHERE 表名2 = 'O_OrderItems'

-- SELECT * FROM #tb1 WHERE 主键 = 1
-- SELECT * FROM #tb2 WHERE 主键 = 1

里面按照自己的理解有些改动,感谢邹建老师~