如何快速比较SQL Server两个不同数据库明细表之间的字段差异
当前位置：点晴教程→点晴OA办公管理信息系统
→『 经验分享&问题答疑 』
用以下几个SQL，可以快速查出两个不同数据库明细表之间的差异：
1、看看两个表的字段数量是否一致：
select count(name) from syscolumns where id=object_id('表名');
2、如果字段数量不同，那么继续用以下SQL获取字段名称和属性，做对比即可：
select name from syscolumns where id=(select id from sysobjects where name='表名')
或者更精确的获取数据类型：
select name as '字段名称',(CASE WHEN type=56 THEN 'int' WHEN type=39 THEN 'nvarchar' WHEN type=38 THEN 'tinyint' WHEN type=35 THEN 'ntext' WHEN type=106 THEN 'datetime' WHEN type=111 THEN 'datetime' WHEN type=108 THEN 'numeric' ELSE 'unkown' END) as '类型',prec as '长度',scale as '小数位',type as '原始类型' from syscolumns where id=(select id from sysobjects where name='表名');
3、比较两个不同数据库A、数据库B相应表的差异（查询表对应的字段是否一致）
本部分是基于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
例子：
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
4、生成自定义SQL，增加缺少的字段：
IF NOT EXISTS (select syscolumns.name from syscolumns RIGHT OUTER JOIN sysobjects ON syscolumns.id = sysobjects.id where (sysobjects.name='表名') AND (syscolumns.name='字段名')) alter TABLE 表名 ADD 字段名 int DEFAULT 0;
IF NOT EXISTS (select syscolumns.name from syscolumns RIGHT OUTER JOIN sysobjects ON syscolumns.id = sysobjects.id where (sysobjects.name='表名') AND (syscolumns.name='字段名')) alter TABLE 表名 ADD 字段名 tinyint DEFAULT 0;
IF NOT EXISTS (select syscolumns.name from syscolumns RIGHT OUTER JOIN sysobjects ON syscolumns.id = sysobjects.id where (sysobjects.name='表名') AND (syscolumns.name='字段名')) alter TABLE 表名 ADD 字段名 nvarchar(50);
IF NOT EXISTS (select syscolumns.name from syscolumns RIGHT OUTER JOIN sysobjects ON syscolumns.id = sysobjects.id where (sysobjects.name='表名') AND (syscolumns.name='字段名')) alter TABLE 表名 ADD 字段名 Decimal(18,2) DEFAULT 0;
IF NOT EXISTS (select syscolumns.name from syscolumns RIGHT OUTER JOIN sysobjects ON syscolumns.id = sysobjects.id where (sysobjects.name='表名') AND (syscolumns.name='字段名')) alter TABLE 表名 ADD 字段名 numeric(10, 2) DEFAULT 0;
IF NOT EXISTS (select syscolumns.name from syscolumns RIGHT OUTER JOIN sysobjects ON syscolumns.id = sysobjects.id where (sysobjects.name='表名') AND (syscolumns.name='字段名')) alter TABLE 表名 ADD 字段名 DateTime DEFAULT getdate();
IF NOT EXISTS (select syscolumns.name from syscolumns RIGHT OUTER JOIN sysobjects ON syscolumns.id = sysobjects.id where (sysobjects.name='表名') AND (syscolumns.name='字段名')) alter TABLE 表名 ADD 字段名 nvarchar(MAX);
IF NOT EXISTS (select syscolumns.name from syscolumns RIGHT OUTER JOIN sysobjects ON syscolumns.id = sysobjects.id where (sysobjects.name='表名') AND (syscolumns.name='字段名')) alter TABLE 表名 ADD 字段名 ntext;
该文章在 2022/4/23 9:12:37 编辑过 |
关键字查询
相关文章
正在查询... 
|
400 186 1886
