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

 

转:http://blog.youkuaiyun.com/zjcxc/archive/2004/01/04/20088.aspx

转:http://topic.youkuaiyun.com/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

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