网上找了一圈对比库的工具，能手工生成差别的SQL的工具没有，改造了一下网上的sql
1, 获取字段名的类型
create FUNCTION [dbo].[getColType](@tab varchar(100),@col varchar(100))
Returns nvarchar(100)
BEGIN
Declare @lx VARCHAR(100)
Declare @Length INT
Declare @Scale int
Declare @code nvarchar(32)
SELECT
@lx = b.name,
@Length = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
@Scale = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
left join
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0
where
d.name=@tab --如果只查询指定表,加上此条件
AND a.name=@col
SET @code=@lx
IF @lx LIKE 'decimal'
SET @code =@lx+'('+ CAST(@Length AS VARCHAR(10))+','+CAST(@Scale AS VARCHAR(10)) +')'
IF @lx LIKE '%varchar'
BEGIN
IF @Length=-1
SET @code =@lx+'(max)'
ELSE
SET @code =@lx+'('+ CAST(@Length AS varchar(10))+')'
end
Return @code
end
2 对比生成SQL，这个存储过程特定情况的，网友要用，需要手工改造一下
create proc
[dbo].[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,默认值
sql_variant,字段说明 sql_variant)
create table #tb2(表名2
varchar(250),字段名
varchar(250),序号
int,标识
bit,主键
bit,类型
varchar(250),
占用字节数
int,长度
int,小数位数
int,允许空
bit,默认值
sql_variant,字段说明 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+'..sysobjects
where xtype=''PK''
and parent_obj=a.id and name in (
SELECT
name FROM '+@dbname1+'..sysindexes
WHERE indid in(
SELECT
indid FROM '+@dbname1+'..sysindexkeys
WHERE id = a.id AND colid=a.colid
)))
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 sys.extended_properties g
ON
a.ID=g.major_id
AND a.COLID=g.minor_id
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+'..sysobjects
where xtype=''PK''
and parent_obj=a.id and name in (
SELECT
name FROM '+@dbname2+'..sysindexes
WHERE indid in(
SELECT
indid FROM '+@dbname2+'..sysindexkeys
WHERE id = a.id AND colid=a.colid
)))
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 sys.extended_properties g
ON
a.ID=g.major_id
AND a.COLID=g.minor_id
order
by a.id,a.colorder')
--and not exists (select 1 from #tb2 where 表名2=a.表名1)
SELECT * INTO #diff FROM (SELECT 比较结果,表名1,字段名1, CAST(默认值1 AS VARCHAR(300)) 默认值11 FROM (
select 比较结果=case when a.表名1
is null and b.序号=1
then '线上库缺少表：'+b.表名2
when b.表名2
is null and a.序号=1
then '线下库缺少表:'+a.表名1
when a.字段名
is null and exists(select 1
from #tb1
where 表名1=b.表名2)
then '线上库 ['+b.表名2+'] 缺少字段：'+b.字段名
when b.字段名
is null and exists(select 1
from #tb2
where 表名2=a.表名1)
then '线下库缺少字段：'+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,
a.表名1, a.字段名 字段名1,b.表名2, b.字段名 字段名2,a.默认值 默认值1,b.默认值 默认值2
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.字段说明
) t1 WHERE (t1.比较结果 LIKE ('%线下库%') OR t1.比较结果 LIKE ('%默认值%') ) AND t1.比较结果 NOT like '%Sequence_20151207' ) t;
SELECT * FROM #diff
DECLARE @diff VARCHAR(300)
DECLARE @tabname VARCHAR(300)
DECLARE @colname VARCHAR(300)
DECLARE @defaultvalue VARCHAR(300)
DECLARE cursors CURSOR
FOR SELECT * FROM #diff --查询集合
OPEN cursors
FETCH NEXT FROM cursors INTO @diff, @tabname,@colname,@defaultvalue
WHILE @@fetch_status = 0
BEGIN
--遍历集合
IF @diff LIKE '%线下库缺少表%'
BEGIN
PRINT ' select * into '+@tabname+' from [b_Online]..'+@tabname
PRINT 'GO'
END
IF @diff LIKE '%线下库缺少字段%'
BEGIN
PRINT ' alter table '+@tabname+' add '+@colname+' '+dbo.getColType(@tabname,@colname)
PRINT 'GO'
END
IF @diff LIKE '%默认值不同%'
BEGIN
IF @defaultvalue<>''
BEGIN
PRINT ' alter table [' + @tabname + '] ADD DEFAULT ' + @defaultvalue + ' FOR [' + @colname + ']'
PRINT 'GO'
END
END
--EXEC(@sql)
FETCH NEXT FROM cursors INTO @diff, @tabname,@colname,@defaultvalue
END
CLOSE cursors
deallocate cursors
执行： exec p_comparestructure 'b_Online','b_Offline' 生成的SQL在Print 信息里。