如何快速比較SQL Server兩個不同數(shù)據(jù)庫明細(xì)表之間的字段差異
當(dāng)前位置：點晴教程→點晴OA辦公管理信息系統(tǒng)
→『 經(jīng)驗分享&問題答疑 』
用以下幾個SQL，可以快速查出兩個不同數(shù)據(jù)庫明細(xì)表之間的差異：
1、看看兩個表的字段數(shù)量是否一致：
select count(name) from syscolumns where id=object_id('表名');
2、如果字段數(shù)量不同，那么繼續(xù)用以下SQL獲取字段名稱和屬性，做對比即可：
select name from syscolumns where id=(select id from sysobjects where name='表名')
或者更精確的獲取數(shù)據(jù)類型：
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 '小數(shù)位',type as '原始類型' from syscolumns where id=(select id from sysobjects where name='表名');
3、比較兩個不同數(shù)據(jù)庫A、數(shù)據(jù)庫B相應(yīng)表的差異（查詢表對應(yīng)的字段是否一致）
本部分是基于2寫的：
select * from (
select name
from 數(shù)據(jù)庫A.dbo.syscolumns
where id=(
select id from 數(shù)據(jù)庫A.dbo.sysobjects
where name='表名A')
) T1 FULL OUTER JOIN(
select name from 數(shù)據(jù)庫B.dbo.syscolumns
where id=(
select id from 數(shù)據(jù)庫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 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
