SQLServer比较两个数据库的对象

 

两个变量，表示要比较的数据库名：
@SourceDatabase
@DestinationDatabase

DECLARE @SourceDatabase VARCHAR(50) 
DECLARE @DestinationDatabase VARCHAR(50) 
DECLARE @SQL VARCHAR(MAX) 
     
SELECT @SourceDatabase = 'ABC' 
SELECT @DestinationDatabase = 'XYZ'
     
SELECT @SQL = 
'
	SELECT 
		ISNULL(S.name,D.name) ObjectName
		,CASE  
			WHEN S.object_id IS NULL 
				THEN D.type_desc + '' is missing in the Source Database: '	+ @SourceDatabase + ''' 
			WHEN D.object_id IS NULL 
				THEN S.type_desc +  '' is missing in the Destination Database: ' + @DestinationDatabase + ''' 
		END ''Status'' 
	FROM 
	(
		SELECT * FROM ' + @SourceDatabase + '.SYS.objects  
		WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')
	) AS S  
	FULL OUTER JOIN 
	(
		SELECT * FROM ' + @DestinationDatabase + '.SYS.objects  
		WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')
	) AS D 
	ON S.name = D.name 
		AND S.type = D.type 
	ORDER BY isnull(S.type,D.type)
'  
EXEC (@Sql)