Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
lyc64166
Member

Откуда:
Сообщений: 9
Было свободное время и немного поизучал SQL. Так для меня наиболее подходяще изучать на каком-то практическом примере то решал задачу сравнения данных двух баз через запросы. Получилось следующее. Просьба показать либо ошибки либо серьезные не оптимальные решения. (в конце описание почему сделано так и что не очень получилось.)

Сравнение баз данных с идентичной структурой.

+
DECLARE @bd1 varchar(255), @bd2 varchar(255);
SET @bd2='Kons';

USE test2;
SELECT @bd1=DB_NAME();

--если есть желание смотреть на каком этапе процесс
DECLARE @state_print bit
SET @state_print=1

--список таблиц в базе данных
--в данном случае интересуют только пользовательские таблицы
if OBJECT_ID('tempdb..#spisok_tabl') is NOT NULL DROP TABLE #spisok_tabl
SELECT name 
	INTO #spisok_tabl 
	FROM sys.objects 
		WHERE type='U'

--список колонок по выбранным таблицам в базе данных
if @state_print=1 print '(2) Получение списка колонок'
if OBJECT_ID('tempdb..#tabl_column') is NOT NULL DROP TABLE #tabl_column
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, TABLE_SCHEMA 
	INTO #tabl_column
	FROM information_schema.columns
		WHERE TABLE_NAME IN (SELECT * FROM #spisok_tabl)
	ORDER BY TABLE_NAME, ORDINAL_POSITION

--список колонок переводим в строку
/*FOR XML PATH('') выводит данные в строку
','+   запятая перед CASE заменяет XML-представление в строке на запятые
STUFF удаляет запятую в самом начале общей строки (по факту заменяет кусок строки с позиции 1 символа, длиной 1 символ на '')
При сравнении с использованием EXCEPT типы ntext и image не поддерживается, поэтому приводим поля данных типов к другим типам
*/
if @state_print=1 print '(3) Получение колонок в виде строки'
if OBJECT_ID('tempdb..#tabl_str_column') is NOT NULL DROP TABLE #tabl_str_column
SELECT TABLE_NAME, TABLE_SCHEMA,
	STUFF((SELECT 
		','+ CASE 
			WHEN DATA_TYPE = 'ntext' THEN 'CAST(['+COLUMN_NAME+'] AS NVARCHAR(MAX)) AS '+COLUMN_NAME
			WHEN DATA_TYPE = 'image' THEN 'CAST(['+COLUMN_NAME+'] AS VARBINARY(MAX)) AS '+COLUMN_NAME
			WHEN DATA_TYPE = 'timestamp' THEN 'CAST(['+COLUMN_NAME+'] AS VARBINARY(8)) AS '+COLUMN_NAME --иначе не работает INSERT INTO ##res1
			ELSE '['+COLUMN_NAME+']'
			END
		FROM #tabl_column t2
		WHERE t1.TABLE_NAME=t2.TABLE_NAME
		FOR XML PATH('')),1,1,'') COLUMN_NAME_1
	INTO #tabl_str_column
	FROM #tabl_column t1
	GROUP BY TABLE_NAME, TABLE_SCHEMA


--тексты запросов для сравнения
--Если результат сравнения заносим в временную таблицу, то нужно давать алиас для избежания ошибки "An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name."
if @state_print=1 print '(4) Получение таблицы с тексами запросов'
if OBJECT_ID('tempdb..#tb_txt_zapros') is NOT NULL DROP TABLE #tb_txt_zapros
SELECT --top 200
	 TABLE_NAME,
	'SELECT '''+[TABLE_NAME]+'='+@bd1+''' AS TableName_BD,'+[COLUMN_NAME_1]+' INTO #res1 FROM '+@bd1+'.'+[TABLE_SCHEMA]+'.'+[TABLE_NAME]+' EXCEPT SELECT '''+[TABLE_NAME]+'='+@bd1+''' AS TableName_BD,'+[COLUMN_NAME_1]+' FROM '+@bd2+'.'+[TABLE_SCHEMA]+'.'+[TABLE_NAME] str1,
	'SELECT '''+[TABLE_NAME]+'='+@bd2+''' AS TableName_BD,'+[COLUMN_NAME_1]+' INTO #res2 FROM '+@bd2+'.'+[TABLE_SCHEMA]+'.'+[TABLE_NAME]+' EXCEPT SELECT '''+[TABLE_NAME]+'='+@bd2+''' AS TableName_BD,'+[COLUMN_NAME_1]+' FROM '+@bd1+'.'+[TABLE_SCHEMA]+'.'+[TABLE_NAME] str2
INTO #tb_txt_zapros
FROM #tabl_str_column

--итоговая таблица с результатами различий
if OBJECT_ID('tempdb..#itog_tabl') is NOT NULL DROP TABLE #itog_tabl
CREATE TABLE #itog_tabl (
	zTABLE_NAME varchar(max) not null,
	zKolObStrok int not null,
	zStrOnlyIn_BD1 int not null,
	zStrOnlyIn_BD2 int not null
)

declare @v0 nvarchar(MAX), @v1 nvarchar(MAX), @v2 nvarchar(MAX)
declare @v1_mod nvarchar(MAX), @v2_mod nvarchar(MAX)
declare @zapr_tmp nvarchar(MAX)
declare @only_1 int, @only_2 int, @kol_zap_tb_bd1 int

declare @kol int, @all varchar(MAX), @all_int int
set @kol=0
SELECT @all_int = COUNT(*) FROM #tb_txt_zapros
set @all = CONVERT(varchar(10), @all_int)

--перебор для выполнения динамических запросов
DECLARE curs CURSOR
	FOR SELECT * FROM #tb_txt_zapros
OPEN curs
FETCH NEXT FROM curs INTO @v0, @v1, @v2
WHILE @@FETCH_STATUS = 0
BEGIN
	if @state_print=1 
		BEGIN
			set @kol=@kol+1
			print '(5) Курсор при получении итоговой таблицы. ('+@v0+') '+CONVERT(varchar(10),@kol)+' / '+@all
		END

--Если в курсоре тупо делать EXEC(@v1) и EXEC(@v2), то будут выводиться все результаты сравнения (даже если количество 
--различающихся строк 0), при этом в SSMS количество выводимых таблиц имеет ограничение. 
--Поэтому собираем таблицу с различающимися данными.
-- https://www.sql.ru/faq/faq_topic.aspx?fid=104	
	SET @v1_mod = @v1 +' SELECT @only_1 = COUNT(*) FROM #res1'
	EXEC sp_executesql @v1_mod, N'@only_1 int out', @only_1=@only_1 out

	SET @v2_mod = @v2 +' SELECT @only_2 = COUNT(*) FROM #res2'
	EXEC sp_executesql @v2_mod, N'@only_2 int out', @only_2=@only_2 out

	SET @zapr_tmp = 'SELECT @kol_zap_tb_bd1 = COUNT(*) FROM '+@v0
	EXEC sp_executesql @zapr_tmp, N'@kol_zap_tb_bd1 int out', @kol_zap_tb_bd1=@kol_zap_tb_bd1 out

	INSERT #itog_tabl VALUES(@v0, @kol_zap_tb_bd1-@only_1, @only_1, @only_2)

	FETCH NEXT FROM curs INTO @v0, @v1, @v2
END

CLOSE curs;
DEALLOCATE curs;

SELECT * FROM #itog_tabl WHERE zStrOnlyIn_BD1<>0 OR zStrOnlyIn_BD2<>0 ORDER BY zTABLE_NAME


--вывод различий
--declare @v0 nvarchar(MAX), @v1 nvarchar(MAX), @v2 nvarchar(MAX)
--declare @v1_mod nvarchar(MAX), @v2_mod nvarchar(MAX)

set @kol=0
SELECT @all_int = COUNT(*) FROM #itog_tabl WHERE zStrOnlyIn_BD1<>0 OR zStrOnlyIn_BD2<>0
set @all = CONVERT(varchar(10), @all_int)

DECLARE curs CURSOR
	FOR SELECT * FROM #tb_txt_zapros WHERE TABLE_NAME IN (SELECT zTABLE_NAME FROM #itog_tabl WHERE zStrOnlyIn_BD1<>0 OR zStrOnlyIn_BD2<>0)
OPEN curs
FETCH NEXT FROM curs INTO @v0, @v1, @v2
WHILE @@FETCH_STATUS = 0
BEGIN

	if @state_print=1 
		BEGIN
			set @kol=@kol+1
			print '(6) Курсор при выводе различий. ('+@v0+') '+CONVERT(varchar(10),@kol)+' / '+@all
		END

/*
	--Вариант 1. Простой вывод различий. убираем из текста запроса сохранение в временную таблицу.
	--Нужно учесть что различия по одной таблице, но в разных базах будут выводиться отдельно.
	SET @v1_mod = REPLACE(@v1, 'INTO #res1', '')
	EXEC (@v1_mod)

	SET @v2_mod = REPLACE(@v2, 'INTO #res2', '')
	EXEC (@v2_mod)
*/

	--Вариант 2. Различия по одной таблице выводим вместе.
	--Оба запроса в глобальную временную таблицу.
	--Чтобы работал INSERT INTO ##res1 необходимо переопределять поля с типом timestamp (оно же rowversion)
	if OBJECT_ID('tempdb..##res1') is NOT NULL DROP TABLE ##res1

	SET @v1_mod = REPLACE(@v1, 'INTO #res1', 'INTO ##res1')
	EXEC (@v1_mod)

	--На случай если различающиеся строки есть только в таблице второй базы.
	if OBJECT_ID('tempdb..##res1') is NOT NULL
		SET @v2_mod = 'INSERT INTO ##res1 ' + REPLACE(@v2, 'INTO #res2', '')
		ELSE SET @v2_mod = REPLACE(@v2, 'INTO #res2', 'INTO ##res1')
	EXEC (@v2_mod)

	SELECT * FROM ##res1


	FETCH NEXT FROM curs INTO @v0, @v1, @v2
END

CLOSE curs;
DEALLOCATE curs;

if OBJECT_ID('tempdb..#spisok_tabl') is NOT NULL DROP TABLE #spisok_tabl
if OBJECT_ID('tempdb..#tabl_column') is NOT NULL DROP TABLE #tabl_column
if OBJECT_ID('tempdb..#tabl_str_column') is NOT NULL DROP TABLE #tabl_str_column
if OBJECT_ID('tempdb..#tb_txt_zapros') is NOT NULL DROP TABLE #tb_txt_zapros
if OBJECT_ID('tempdb..#itog_tabl') is NOT NULL DROP TABLE #itog_tabl
if OBJECT_ID('tempdb..##res1') is NOT NULL DROP TABLE ##res1



Примечания.
- делал пошагово - мне так проще было понимать, плюс возможность делать процесс сравнения пошагово с возможностью посмотреть результаты промежуточных этапов.
- использование @state_print - так как я почти не работал ранее с очень длительными процессами, то мне грустно смотреть на зеленый кружочек и я хотел смотреть где движется процесс.
- первоначально было желание результат динамического запроса выводить в временную таблицу (типа INSERT INTO #tb_tmp EXEC(@v1)), но оказалось что временная таблица (#tb_tmp) должна быть определена со всеми столбцами. А я это не смог реализовать простыми методами, а усложнения типа применения OPENROWSET или EXEC AT решил не использовать. То есть встала проблема простого определения динамически создаваемой таблицы. В итоге решил применять глобальную временную таблицу.

Сам вижу что получение @bd1 и @bd2 несколько кривоватое. Возможно есть более красивые и оптимальные варианты.

Плюс в варианте 2 вывода различий проявляется ошибка обрезания данных если длина наименования @bd1 короче длины @bd2. То есть если в результате EXEC(@v1_mod) поле TableName_BD будет короче чем при применении EXEC(@v2_mod) , то получаем "String or binary data would be truncated". Как это обойти я не понял.

Сообщение было отредактировано: 15 окт 19, 13:38
15 окт 19, 13:29    [21994581]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7401
lyc64166,

в Visual Studio есть инструменты сравнения схем объектов и данных.
15 окт 19, 14:01    [21994643]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
lyc64166
Member

Откуда:
Сообщений: 9
Владислав Колосов,

Еще есть продукт от devart который хоть и платный, но имеет 30 дней пробного периода.
Но мне было интересно разобраться в SQL. Поэтому возник этот велосипед.
15 окт 19, 16:29    [21994796]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
у вас никакое не сравнение баз,
а только сравнение таблиц, и то неполное.

таблицы - это не только колонки.
это еще и индексы, констрэйнты и триггеры.

а база это и еще туча объектов.

откройте Object Explorer в студии и ужаснитесь, сколько всего есть в базах,
да и в таблицах тоже
15 окт 19, 16:58    [21994837]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
lyc64166
Member

Откуда:
Сообщений: 9
Yasha123,

То есть существуют инструменты которые сравнивают "еще и индексы, констрэйнты и триггеры"?
Особенно интересно про индексы. Таблицы индексов сравниваются в принципе? Или речь идет о сравнении схемы?

Вроде бы в первом сообщении сразу указано что это для "Сравнение баз данных с идентичной структурой".
16 окт 19, 11:53    [21995312]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3146
lyc64166,

В Visual Studio, помимо Schema Compare, есть еще и Data Compare. Но, конечно, если у вас терабайтные базы, то может и не взлететь - она данные для сравнения на клиента тянет, насколько я помню.

И да, там много настроек сравнения.
16 окт 19, 11:56    [21995319]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
lyc64166
Member

Откуда:
Сообщений: 9
Насколько я понял желающих ковырять тексты запросов нет.

Тогда пара конкретных вопросов.
1. Можно ли задать двумя переменными имена баз данных (@bd1 и @bd2), а затем одну из переменных использовать в конструкции USE @bd1? Или каким-то другим образом перейти в контекст базы данных заданной в переменной?
2. Конструкцией SELECT * INTO #tmp FROM получаем временную таблицу. Можно ли в этой таблице изменить (увеличить) тип поля nvarchar? Потому как при добавлении данных в эту таблицу с помощью INSERT INTO #tmp если в поле с типом nvarchar новые данные превышают максимальное значение уже имеющихся данных, то выпадает сообщение об ошибке.
16 окт 19, 12:07    [21995333]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7401
lyc64166,

все отчеты есть в справке. И прекратите заниматься интуитивным программированием.
16 окт 19, 12:24    [21995357]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 306
ER/Studio пару недель вам все посравнивает бесплатно. Может и подольше, если заморочитесь.

TOAD Data modeller вероятно тоже.
Сделайте reverse Engineering одной базы и сравните с другой.
17 окт 19, 00:39    [21995955]     Ответить | Цитировать Сообщить модератору
 Re: Сравнение идентичных баз через запрос. Возможные ошибки и не оптимальные решения.  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 306
А если вам Count ы нужны, то вот
https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database
17 окт 19, 00:48    [21995960]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить