SQL比较数据库之间数据表缺失情况

说明：根据邹建老师的对比示例修改

IF EXISTS ( SELECT *
                FROM dbo.sysobjects
                WHERE id = OBJECT_ID(N'[dbo].[sp_CompareStructure]') AND OBJECTPROPERTY(id,
                                                              N'IsProcedure') = 1 )
    DROP PROCEDURE [dbo].[sp_CompareStructure]
 GO
 
CREATE PROC sp_CompareStructure @dbname1 VARCHAR(250), --要比较的数据库名1
    @dbname2 VARCHAR(250) --要比较的数据库名2
AS
CREATE TABLE #tb1 (TName VARCHAR(250), IncNo INT)
 
CREATE TABLE #tb2 (TName VARCHAR(250), IncNo INT)
 
 --得到数据库1用户表列表
EXEC('INSERT INTO #tb1
      SELECT TName = name, IncNo = uid FROM ' + @dbname1 + '..sysobjects WHERE type = ''U'' ORDER BY name')
 
 --得到数据库2用户表列表
EXEC('INSERT INTO #tb2
      SELECT TName = name, IncNo = uid FROM ' + @dbname2 + '..sysobjects WHERE type = ''U'' ORDER BY name')
SELECT 比较结果 = CASE WHEN a.TName IS NULL AND b.IncNo = 1 THEN '库1缺少表：' + b.TName
                   WHEN b.TName IS NULL AND a.IncNo = 1 THEN '库2缺少表:' + a.TName
                   ELSE ''
              END, *
    FROM #tb1 a
    FULL JOIN #tb2 b ON a.TName = b.TName
    WHERE a.TName IS NULL OR b.TName IS NULL
    ORDER BY ISNULL(a.TName, b.TName), ISNULL(a.IncNo, b.IncNo)
GO

--调用示例

EXEC sp_CompareStructure 'AA','BB'