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) --要比较的数据库名2ASCREATE 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'