مقایسه دو دیتابیس در SQL Server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

DECLARE @SourceDB VARCHAR(50) 
DECLARE @DestinationDB VARCHAR(50) 
DECLARE @SQL VARCHAR(MAX) 
SELECT @SourceDB = 'HesSiteDB' 
SELECT @DestinationDB = 'InsuranceDB'
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: '	+ @SourceDB + ''' 
			WHEN D.object_id IS NULL 
				THEN S.type_desc +  '' is missing in the Destination Database: ' + @DestinationDB + ''' 
		END ''Status'' 
	FROM 
	(
		SELECT * FROM ' + @SourceDB + '.SYS.objects  
		WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')
	) AS S  
	FULL OUTER JOIN 
	(
		SELECT * FROM ' + @DestinationDB + '.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)