У меня две базы данных с именем DB1 и DB2 на сервере Sql 2008. Эти две базы данных имеют одинаковые таблицы и одни и те же данные таблицы. Тем не менее, я хочу проверить, есть ли какие-либо различия между данными в этих таблицах.
Может ли кто-нибудь помочь мне с помощью script для этого?
select *
from (
select *
from DB1.dbo.Table
except
select *
from DB2.dbo.Table
) as T
union all
select *
from (
select *
from DB2.dbo.Table
except
select *
from DB1.dbo.Table
) as T
Тестовый код:
declare @T1 table (ID int)
declare @T2 table (ID int)
insert into @T1 values(1),(2)
insert into @T2 values(2),(3)
select *
from (
select *
from @T1
except
select *
from @T2
) as T
union all
select *
from (
select *
from @T2
except
select *
from @T1
) as T
Результат:
ID
-----------
1
3
Id действительно предлагает людям, которые сталкиваются с этой проблемой, найти и найти сторонний инструмент сравнения баз данных.
Причина - эти инструменты экономят много времени и делают процесс менее подверженным ошибкам.
Я использовал инструменты сравнения из ApexSQL (Diff и Data Diff), но вы не можете ошибиться с другими инструментами marc_s, и Марина Настенко уже указала.
Если вы абсолютно уверены, что только собираетесь сравнивать таблицы, тогда SQL отлично, но если вам понадобится время от времени, вам будет лучше с помощью стороннего инструмента.
Если у вас нет бюджета, чтобы купить его, просто используйте его в пробном режиме, чтобы выполнить задание.
Я надеюсь, что новые читатели найдут это полезным, хотя его поздний ответ...
Я делал так, используя функцию Checksum (*)
В Essance он создает контрольную сумму уровня на всех данных столбцов, затем вы можете сравнить контрольную сумму каждой строки для каждой таблицы друг с другом, использовать левое соединение, чтобы найти строки, которые отличаются.
Надеюсь, что это имело смысл...
Лучше с примером....
select *
from
( select checksum(*) as chk, userid as k from UserAccounts) as t1
left join
( select checksum(*) as chk, userid as k from UserAccounts) as t2 on t1.k = t2.k
where t1.chk <> t2.chk
Сравнение двух баз данных в базе данных SQL. Попробуйте этот запрос, который может помочь.
SELECT T.[name] AS [table_name], AC.[name] AS [column_name], TY.[name] AS
system_data_type FROM [***Database Name 1***].sys.[tables] AS T
INNER JOIN [***Database Name 1***].sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN [***Database Name 1***].sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
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
INNER JOIN ***Database Name 2***.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN ***Database Name 2***.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
select * from DB1.dbo.Table a inner join DB2.dbo.Table b on b.PrimKey = a.PrimKey
where a.FirstColumn <> b.FirstColumn ...
Контрольная сумма, которую Мэтт рекомендовал, вероятно, лучше подходит для сравнения столбцов, а не для сравнения каждого столбца
Если база данных находится на одном сервере, используйте формат [DatabaseName].[Owner].[TableName] при обращении к таблице, которая находится в другой базе данных.
Например: [DB1].[dbo].[TableName]
Если базы данных на разных серверах выглядят на Создание связанных серверов (механизм SQL Server Database)
Другое решение (не T-SQL): вы можете использовать утилиту tablediff.
Например, если вы хотите сравнить две таблицы (Localitate) с двух разных серверов (ROBUH01 и ROBUH02), вы можете использовать эту команду оболочки:
C:\Program Files\Microsoft SQL Server\100\COM>tablediff -sourceserver ROBUH01 -s
ourcedatabase SIM01 -sourceschema dbo -sourcetable Localitate -destinationserver
ROBUH02 -destinationschema dbo -destinationdatabase SIM02 -destinationtable Lo
calitate
Результаты:
Microsoft (R) SQL Server Replication Diff Tool Copyright (c) 2008 Microsoft Corporation User-specified agent parameter values:
-sourceserver ROBUH01
-sourcedatabase SIM01
-sourceschema dbo
-sourcetable Localitate
-destinationserver ROBUH02
-destinationschema dbo
-destinationdatabase SIM02
-destinationtable Localitate
Table [SIM01].[dbo].[Localitate] on ROBUH01 and Table [SIM02].[dbo].[Localitate ] on ROBUH02 have 10 differences.
Err Id Dest.
Only 21433 Dest.
Only 21434 Dest.
Only 21435 Dest.
Only 21436 Dest.
Only 21437 Dest.
Only 21438 Dest.
Only 21439 Dest.
Only 21441 Dest.
Only 21442 Dest.
Only 21443
The requested operation took 9,9472657 seconds.
------------------------------------------------------------------------
Если обе базы данных на одном сервере. Вы можете проверить похожие таблицы, используя следующий запрос:
select
fdb.name, sdb.name
from
FIRSTDBNAME.sys.tables fdb
join SECONDDBNAME.sys.tables sdb
on fdb.name = sdb.name -- compare same name tables
order by
1
Выбирая аналогичную таблицу, вы можете сравнить схему столбцов с помощью представления sys.columns.
Надеюсь, это поможет вам.
Чтобы сравнить две базы данных, я написал приведенные ниже процедуры. Если вы хотите сравнить две таблицы, вы можете использовать процедуру "Сравнить таблицы". Пример:
EXEC master.dbo.CompareTables 'DB1', 'dbo', 'table1', 'DB2', 'dbo', 'table2'
Если вы хотите сравнить две базы данных, используйте процедуру "Сравнить базы данных". Пример:
EXEC master.dbo.CompareDatabases 'DB1', 'DB2'
Примечание. - Я пытался сделать процедуры безопасными, но в любом случае эти процедуры предназначены только для тестирования и отладки. - Если вы хотите, чтобы полное решение для сравнения использовало сторонние пользователи (Visual Studio,...)
USE [master]
GO
create proc [dbo].[CompareDatabases]
@FirstDatabaseName nvarchar(50),
@SecondDatabaseName nvarchar(50)
as
begin
-- Check that databases exist
if not exists(SELECT name FROM sys.databases WHERE [email protected]Name)
return 0
if not exists(SELECT name FROM sys.databases WHERE [email protected])
return 0
declare @result table (TABLE_NAME nvarchar(256))
SET NOCOUNT ON
insert into @result EXEC('(Select distinct TABLE_NAME from ' + @FirstDatabaseName + '.INFORMATION_SCHEMA.COLUMNS '
+'Where TABLE_SCHEMA=''dbo'')'
+ 'intersect'
+ '(Select distinct TABLE_NAME from ' + @SecondDatabaseName + '.INFORMATION_SCHEMA.COLUMNS '
+'Where TABLE_SCHEMA=''dbo'')')
DECLARE @TABLE_NAME nvarchar(256)
DECLARE curseur CURSOR FOR
SELECT TABLE_NAME FROM @result
OPEN curseur
FETCH curseur INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
print 'TABLE : ' + @TABLE_NAME
EXEC master.dbo.CompareTables @FirstDatabaseName, 'dbo', @TABLE_NAME, @SecondDatabaseName, 'dbo', @TABLE_NAME
FETCH curseur INTO @TABLE_NAME
END
CLOSE curseur
DEALLOCATE curseur
SET NOCOUNT OFF
end
GO
.
USE [master]
GO
CREATE PROC [dbo].[CompareTables]
@FirstTABLE_CATALOG nvarchar(256),
@FirstTABLE_SCHEMA nvarchar(256),
@FirstTABLE_NAME nvarchar(256),
@SecondTABLE_CATALOG nvarchar(256),
@SecondTABLE_SCHEMA nvarchar(256),
@SecondTABLE_NAME nvarchar(256)
AS
BEGIN
-- Verify if first table exist
DECLARE @table1 nvarchar(256) = @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME
DECLARE @return_status int
EXEC @return_status = master.dbo.TableExist @FirstTABLE_CATALOG, @FirstTABLE_SCHEMA, @FirstTABLE_NAME
IF @return_status = 0
BEGIN
PRINT @table1 + ' : Table Not FOUND'
RETURN 0
END
-- Verify if second table exist
DECLARE @table2 nvarchar(256) = @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME
EXEC @return_status = master.dbo.TableExist @SecondTABLE_CATALOG, @SecondTABLE_SCHEMA, @SecondTABLE_NAME
IF @return_status = 0
BEGIN
PRINT @table2 + ' : Table Not FOUND'
RETURN 0
END
-- Compare the two tables
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = '('
+ '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
+ 'EXCEPT'
+ '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
+ ')'
+ 'UNION'
+ '('
+ '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
+ 'EXCEPT'
+ '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
+ ')'
DECLARE @wrapper AS NVARCHAR(MAX) = 'if exists (' + @sql + ')' + char(10) + ' (' + @sql + ')ORDER BY 2'
Exec(@wrapper)
END
GO
.
USE [master]
GO
CREATE PROC [dbo].[TableExist]
@TABLE_CATALOG nvarchar(256),
@TABLE_SCHEMA nvarchar(256),
@TABLE_NAME nvarchar(256)
AS
BEGIN
IF NOT EXISTS(SELECT name FROM sys.databases WHERE [email protected]_CATALOG)
RETURN 0
declare @result table (TABLE_SCHEMA nvarchar(256), TABLE_NAME nvarchar(256))
SET NOCOUNT ON
insert into @result EXEC('Select TABLE_SCHEMA, TABLE_NAME from ' + @TABLE_CATALOG + '.INFORMATION_SCHEMA.COLUMNS')
SET NOCOUNT OFF
IF EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME FROM @result
WHERE [email protected]_SCHEMA AND [email protected]_NAME)
RETURN 1
RETURN 0
END
GO
Я ищу для сравнения 2 таблицы с разных серверов (разные исходные системы)
Необходимо проверить правильность данных после интеграции данных из прежней системы в новую целевую систему. Пожалуйста, помогите
Есть инструменты, которые могут сделать это за вас. https://www.monitorplace.com/, например.