判断两个数据库中不一样的表和存储过程-优快云博客

本文提供了一系列SQL查询脚本，用于对比两个数据库之间的不同之处，包括表结构差异、缺失的表以及存储过程的不同。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

---两表结构不一样

DECLARE @bigdatabase VARCHAR(20),@smalldatabase VARCHAR(20)
SET @bigdatabase = 'dbf_zhangcun'
SET @smalldatabase = 'dbf_yangting'
DECLARE @sqltext VARCHAR(4000)
SET @sqltext = ' SELECT a.a1,a.a2,a.a3,a.length from '
+ ' (SELECT a.a1,a.a2,a.a3,a.length, b.a1 AS b1,b.a2 AS b2,b.a3 AS b3,b.length AS b4 from '
+ '(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from ' + @bigdatabase + '.sys.syscolumns c '
+ ' inner join ' + @bigdatabase + '.sys.systypes t on c.xtype= t.xtype '
+ ' inner join ' + @bigdatabase + '.sys.sysobjects o on c.id= o.id '
+ ' where   o.xtype=''u'' '
+ ')AS a'
+ ' LEFT JOIN '
+ ' (select o.name AS a1, c.name AS a2,t.name AS a3,c.length from ' + @smalldatabase + '.sys.syscolumns c '
+ ' inner join ' + @smalldatabase + '.sys.systypes t on c.xtype= t.xtype '
+ ' inner join ' + @smalldatabase + '.sys.sysobjects o on c.id= o.id '
+ ' where   o.xtype=''u'''
+ ' )AS b'
+ ' ON a.a1 = b.a1 AND a.a2 = b.a2 '
+ ' )AS a '
+ ' WHERE a.b1 IS NULL GROUP by a.a1,a.a2,a.a3,a.length '
exec(@sqltext)

----两个数据库所缺表
DECLARE @bigdatabase VARCHAR(20),@smalldatabase VARCHAR(20)
SET @bigdatabase = 'dbf_zhangcun'
SET @smalldatabase = 'dbf_yangting'
DECLARE @sqltext VARCHAR(4000)

SET @sqltext = ' SELECT a.a1 from '
+ '(SELECT a.a1,a.a2,a.a3,a.length, b.a1 AS b1,b.a2 AS b2,b.a3 AS b3,b.length AS b4 from '
+ '(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from ' + @bigdatabase + '.sys.syscolumns c '
+ 'inner join ' + @bigdatabase + '.sys.systypes t on c.xtype= t.xtype'
+ ' inner join ' + @bigdatabase + '.sys.sysobjects o on c.id= o.id'
+ ' where   o.xtype=''u'' '
+ ')AS a'
+ ' LEFT JOIN '
+ '(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from ' + @smalldatabase + '.sys.syscolumns c'
+ ' inner join ' + @smalldatabase + '.sys.systypes t on c.xtype= t.xtype'
+ ' inner join ' + @smalldatabase + '.sys.sysobjects o on c.id= o.id'
+ ' where   o.xtype=''u'' '
+ ' )AS b'
+ ' ON a.a1 = b.a1 '
+ ' )AS a'
+ ' WHERE a.b1 IS NULL GROUP BY a.a1'
exec(@sqltext)

---两个数据库所缺存储过程

DECLARE @bigdatabase VARCHAR(20),@smalldatabase VARCHAR(20)
SET @bigdatabase = 'dbf_zhangcun'
SET @smalldatabase = 'dbf_yangting'
DECLARE @sqltext VARCHAR(4000)

SET @sqltext = ' SELECT a.name,a.b1 from '
+ '('
+ ' SELECT a.name,b.name AS b1 from'
+ '(SELECT a.name FROM ' + @bigdatabase + '.sys.sysobjects AS a WHERE xtype = ''P'')AS a'
+ ' LEFT join'
+ '(SELECT a.name FROM ' + @smalldatabase + '.sys.sysobjects AS a WHERE xtype = ''P'')AS b'
+ ' ON a.name = b.NAME'
+ ')AS a WHERE a.b1 IS NULL'
exec(@sqltext)

---两表结构不一样
SELECT a.a1,a.a2,a.a3,a.length from
(SELECT a.a1,a.a2,a.a3,a.length, b.a1 AS b1,b.a2 AS b2,b.a3 AS b3,b.length AS b4 from
(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from dbf.sys.syscolumns c
inner join dbf.sys.systypes t on c.xtype= t.xtype
inner join dbf.sys.sysobjects o on c.id= o.id
where   o.xtype='u'
)AS a
LEFT JOIN
(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from hisdb.sys.syscolumns c
inner join hisdb.sys.systypes t on c.xtype= t.xtype
inner join hisdb.sys.sysobjects o on c.id= o.id
where   o.xtype='u'
)AS b
ON a.a1 = b.a1 AND a.a2 = b.a2
)AS a
WHERE a.b1 IS NULL GROUP by a.a1,a.a2,a.a3,a.length

----两个数据库所缺表
SELECT a.a1 from
(SELECT a.a1,a.a2,a.a3,a.length, b.a1 AS b1,b.a2 AS b2,b.a3 AS b3,b.length AS b4 from
(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from dbf.sys.syscolumns c
inner join dbf.sys.systypes t on c.xtype= t.xtype
inner join dbf.sys.sysobjects o on c.id= o.id
where   o.xtype='u'
)AS a
LEFT JOIN
(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from hisdb.sys.syscolumns c
inner join hisdb.sys.systypes t on c.xtype= t.xtype
inner join hisdb.sys.sysobjects o on c.id= o.id
where   o.xtype='u'
)AS b
ON a.a1 = b.a1
)AS a
WHERE a.b1 IS NULL GROUP BY a.a1

---两个数据库所缺存储过程
SELECT a.name,a.b1 from
(
SELECT a.name,b.name AS b1 from
(SELECT a.name FROM dbf.sys.sysobjects AS a WHERE xtype = 'P')AS a
LEFT join
(SELECT a.name FROM hisdb.sys.sysobjects AS a WHERE xtype = 'P')AS b
ON a.name = b.name
)AS a WHERE a.b1 IS null

-----查询同样的两表长度不一样

SELECT a.a1,a.a2,a.a3,a.length,a.b1,a.b2,a.b3,a.b4 from
(SELECT a.a1,a.a2,a.a3,a.length, b.a1 AS b1,b.a2 AS b2,b.a3 AS b3,b.length AS b4 from
(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from dbf.sys.syscolumns c
inner join dbf.sys.systypes t on c.xtype= t.xtype
inner join dbf.sys.sysobjects o on c.id= o.id
where o.xtype='u'
)AS a
LEFT JOIN
(select o.name AS a1, c.name AS a2,t.name AS a3,c.length from dbf_sunjiatuan.sys.syscolumns c
inner join dbf_sunjiatuan.sys.systypes t on c.xtype= t.xtype
inner join dbf_sunjiatuan.sys.sysobjects o on c.id= o.id
where o.xtype='u'
)AS b
ON a.a1 = b.a1 AND a.a2 = b.a2
)AS a
WHERE a.length < a.b4 and a.a1 LIKE 'sf_%'
GROUP by a.a1,a.a2,a.a3,a.length,a.b1,a.b2,a.b3,a.b4