如何快速比較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
