· 6 years ago · Jun 15, 2019, 05:01 AM
1select *
2from (
3 select *
4 from DB1.dbo.Table
5 except
6 select *
7 from DB2.dbo.Table
8 ) as T
9union all
10select *
11from (
12 select *
13 from DB2.dbo.Table
14 except
15 select *
16 from DB1.dbo.Table
17 ) as T
18
19declare @T1 table (ID int)
20declare @T2 table (ID int)
21
22insert into @T1 values(1),(2)
23insert into @T2 values(2),(3)
24
25select *
26from (
27 select *
28 from @T1
29 except
30 select *
31 from @T2
32 ) as T
33union all
34select *
35from (
36 select *
37 from @T2
38 except
39 select *
40 from @T1
41 ) as T
42
43ID
44-----------
451
463
47
48select *
49from
50( select checksum(*) as chk, userid as k from UserAccounts) as t1
51left join
52( select checksum(*) as chk, userid as k from UserAccounts) as t2 on t1.k = t2.k
53where t1.chk <> t2.chk
54
55SELECT T.[name] AS [table_name], AC.[name] AS [column_name], TY.[name] AS
56 system_data_type FROM [***Database Name 1***].sys.[tables] AS T
57 INNER JOIN [***Database Name 1***].sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
58 INNER JOIN [***Database Name 1***].sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
59 EXCEPT SELECT T.[name] AS [table_name], AC.[name] AS [column_name], TY.[name] AS system_data_type FROM ***Database Name 2***.sys.[tables] AS T
60 INNER JOIN ***Database Name 2***.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
61 INNER JOIN ***Database Name 2***.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
62
63select * from DB1.dbo.Table a inner join DB2.dbo.Table b on b.PrimKey = a.PrimKey
64where a.FirstColumn <> b.FirstColumn ...
65
66C:Program FilesMicrosoft SQL Server100COM>tablediff -sourceserver ROBUH01 -s
67ourcedatabase SIM01 -sourceschema dbo -sourcetable Localitate -destinationserver
68 ROBUH02 -destinationschema dbo -destinationdatabase SIM02 -destinationtable Lo
69calitate
70
71Microsoft (R) SQL Server Replication Diff Tool Copyright (c) 2008 Microsoft Corporation User-specified agent parameter values:
72-sourceserver ROBUH01
73-sourcedatabase SIM01
74-sourceschema dbo
75-sourcetable Localitate
76-destinationserver ROBUH02
77-destinationschema dbo
78-destinationdatabase SIM02
79-destinationtable Localitate
80
81Table [SIM01].[dbo].[Localitate] on ROBUH01 and Table [SIM02].[dbo].[Localitate ] on ROBUH02 have 10 differences.
82
83Err Id Dest.
84Only 21433 Dest.
85Only 21434 Dest.
86Only 21435 Dest.
87Only 21436 Dest.
88Only 21437 Dest.
89Only 21438 Dest.
90Only 21439 Dest.
91Only 21441 Dest.
92Only 21442 Dest.
93Only 21443
94The requested operation took 9,9472657 seconds.
95------------------------------------------------------------------------
96
97select
98 fdb.name, sdb.name
99from
100 FIRSTDBNAME.sys.tables fdb
101 join SECONDDBNAME.sys.tables sdb
102 on fdb.name = sdb.name -- compare same name tables
103order by
104 1
105
106EXEC master.dbo.CompareTables 'DB1', 'dbo', 'table1', 'DB2', 'dbo', 'table2'
107
108EXEC master.dbo.CompareDatabases 'DB1', 'DB2'
109
110USE [master]
111GO
112
113create proc [dbo].[CompareDatabases]
114 @FirstDatabaseName nvarchar(50),
115 @SecondDatabaseName nvarchar(50)
116 as
117begin
118 -- Check that databases exist
119 if not exists(SELECT name FROM sys.databases WHERE name=@FirstDatabaseName)
120 return 0
121 if not exists(SELECT name FROM sys.databases WHERE name=@SecondDatabaseName)
122 return 0
123
124 declare @result table (TABLE_NAME nvarchar(256))
125 SET NOCOUNT ON
126 insert into @result EXEC('(Select distinct TABLE_NAME from ' + @FirstDatabaseName + '.INFORMATION_SCHEMA.COLUMNS '
127 +'Where TABLE_SCHEMA=''dbo'')'
128 + 'intersect'
129 + '(Select distinct TABLE_NAME from ' + @SecondDatabaseName + '.INFORMATION_SCHEMA.COLUMNS '
130 +'Where TABLE_SCHEMA=''dbo'')')
131
132 DECLARE @TABLE_NAME nvarchar(256)
133 DECLARE curseur CURSOR FOR
134 SELECT TABLE_NAME FROM @result
135 OPEN curseur
136 FETCH curseur INTO @TABLE_NAME
137 WHILE @@FETCH_STATUS = 0
138 BEGIN
139 print 'TABLE : ' + @TABLE_NAME
140 EXEC master.dbo.CompareTables @FirstDatabaseName, 'dbo', @TABLE_NAME, @SecondDatabaseName, 'dbo', @TABLE_NAME
141 FETCH curseur INTO @TABLE_NAME
142 END
143 CLOSE curseur
144 DEALLOCATE curseur
145 SET NOCOUNT OFF
146end
147GO
148
149USE [master]
150GO
151
152CREATE PROC [dbo].[CompareTables]
153 @FirstTABLE_CATALOG nvarchar(256),
154 @FirstTABLE_SCHEMA nvarchar(256),
155 @FirstTABLE_NAME nvarchar(256),
156 @SecondTABLE_CATALOG nvarchar(256),
157 @SecondTABLE_SCHEMA nvarchar(256),
158 @SecondTABLE_NAME nvarchar(256)
159 AS
160BEGIN
161 -- Verify if first table exist
162 DECLARE @table1 nvarchar(256) = @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME
163 DECLARE @return_status int
164 EXEC @return_status = master.dbo.TableExist @FirstTABLE_CATALOG, @FirstTABLE_SCHEMA, @FirstTABLE_NAME
165 IF @return_status = 0
166 BEGIN
167 PRINT @table1 + ' : Table Not FOUND'
168 RETURN 0
169 END
170
171
172
173 -- Verify if second table exist
174 DECLARE @table2 nvarchar(256) = @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME
175 EXEC @return_status = master.dbo.TableExist @SecondTABLE_CATALOG, @SecondTABLE_SCHEMA, @SecondTABLE_NAME
176 IF @return_status = 0
177 BEGIN
178 PRINT @table2 + ' : Table Not FOUND'
179 RETURN 0
180 END
181
182 -- Compare the two tables
183 DECLARE @sql AS NVARCHAR(MAX)
184 SELECT @sql = '('
185 + '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
186 + 'EXCEPT'
187 + '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
188 + ')'
189 + 'UNION'
190 + '('
191 + '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
192 + 'EXCEPT'
193 + '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
194 + ')'
195 DECLARE @wrapper AS NVARCHAR(MAX) = 'if exists (' + @sql + ')' + char(10) + ' (' + @sql + ')ORDER BY 2'
196 Exec(@wrapper)
197END
198GO
199
200USE [master]
201GO
202
203CREATE PROC [dbo].[TableExist]
204 @TABLE_CATALOG nvarchar(256),
205 @TABLE_SCHEMA nvarchar(256),
206 @TABLE_NAME nvarchar(256)
207 AS
208BEGIN
209 IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=@TABLE_CATALOG)
210 RETURN 0
211
212 declare @result table (TABLE_SCHEMA nvarchar(256), TABLE_NAME nvarchar(256))
213 SET NOCOUNT ON
214 insert into @result EXEC('Select TABLE_SCHEMA, TABLE_NAME from ' + @TABLE_CATALOG + '.INFORMATION_SCHEMA.COLUMNS')
215 SET NOCOUNT OFF
216
217 IF EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME FROM @result
218 WHERE TABLE_SCHEMA=@TABLE_SCHEMA AND TABLE_NAME=@TABLE_NAME)
219 RETURN 1
220
221 RETURN 0
222END
223
224GO