Как найти недостающие столбцы, ограничения, индексы в базе данных по сравнению с другими
========================================================================================

У меня есть приложение С#.net для Windows, которое использует базу данных в Microsoft SQL Server 2008. Во время развертывания в первый раз для наших клиентов мы создаем копию нашей базы данных и развертываем ее на клиентах, удаленный сервер вместе с приложением пользовательского интерфейса. База данных клиента может быть в версии SQL Server 2005 и выше.

В течение времени приложение UI и связанная с ним база данных внесли много изменений. Поскольку это толстое клиентское приложение, клиентская база данных не синхронизируется с нашей последней базой данных, и, к сожалению, никто никогда не делал заметки о всех сделанных изменениях. Поэтому мои проблемы заключаются в следующем:

* Как найти недостающие столбцы в таблице базы данных в базе данных клиентов по сравнению с моей базой данных? если таковые имеются?
* Как найти отсутствующие первичные/уникальные ограничения в таблице базы данных в базе данных клиентов по сравнению с моей базой данных? если таковые имеются?
* Как найти отсутствующие индексы в таблице базы данных, существующие в базе данных клиентов по сравнению с моей базой данных? если таковые имеются?

Пожалуйста, имейте в виду, что размер базы данных клиентов может варьироваться от 10 до 100 ГБ, поэтому я не могу планировать просто отбрасывать все клиентские таблицы и воссоздавать их.

 

Для этой задачи вам понадобится программное обеспечение, которое сравнивает базу данных SQL. Так же, как есть много программного обеспечения для сравнения текста, есть много, чтобы сравнить базу данных.

Лично я использую [AdoptSQLDiff](http://www.adeptsql.com/), но есть куча. [RedGate также разработал один](http://www.red-gate.com/products/sql-development/sql-data-compare/), и я знаю, что другие существуют. Просто введите SQL Database сравнить в google, чтобы найти их. Вероятно, вы можете выполнить работу с пробным периодом.

Эти программы показывают, какие таблицы были добавлены, удалены или изменены. Он делает то же самое для представлений, индексов, триггеров, хранимых процедур, пользовательских функций, ограничений. Что еще более важно, эти инструменты генерируют script, чтобы вносить изменения в целевую базу данных. Очень удобно, но посмотрите на script сгенерированный, он когда-то испортит его, удалив данные, но его можно легко устранить.

Существует также возможность сравнивать данные в конкретной таблице, если вам нужно.

Вот скриншот интерфейса другого, чтобы вы знали, как он выглядит.
[![введите описание изображения здесь]()](/img/02814474488c748d23eaf12aa173d86d.png)

 

Вы можете использовать [приложения уровня данных](https://msdn.microsoft.com/en-us/library/ee210546.aspx). Это встроенная функция SQL Server, поэтому вам не нужно использовать дополнительные инструменты.

Вы можете извлечь приложение уровня данных из своей базы данных (в SSMS правой кнопкой мыши → Задачи → Извлечь приложение уровня данных) в файл DACPAC, скопировать файл на клиентский сервер и использовать его для обновления базы данных там (или создать обновление script).

Он также прекрасно сочетается с [Инструменты данных SQL Server](https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx).

 

С помощью SQLServer Management Studio вы можете попробовать выбрать базу данных, а затем Task- > Generate Script, выбрав соответствующие параметры.

Сделайте то же самое для 2 дБ, которые вы хотите сравнить. Вы получите два текстовых файла, которые вы можете сравнить с программным сравнением текстового файла.

Сравнение выявит разницу в структуре db.

Не лучший способ сделать это, конечно. Но это может быть началом. Если два dbs не слишком отличаются друг от друга, вы должны иметь возможность обрабатывать различия

Лучшая опция, используйте некоторое программное обеспечение сравнения db. Они предназначены для сравнения структуры db, индексов ограничений и т.д. Никогда не использовали ни один из них, поэтому не можете давать никаких советов по этому поводу.

 

Если в одно время используется какой-либо инструмент diff для DB, [VS2010 + имеет встроенную в одном](https://msdn.microsoft.com/en-us/library/aa833435(v=vs.100).aspx), позволяет получить разницу для схемы и данных в двух разных файлах.

Если вы хотите решить проблему своего процесса разработки, у вас есть широкий спектр возможностей для реализации версий для базы данных.

* Если вы используете EF - [использовать Migrations](https://msdn.microsoft.com/en-us/data/jj591621.aspx), не можете побить это.
* Если вы только на SQL Server и никогда не смотрите на другую СУБД, проверьте DAC (приложения уровня данных, упомянутые Jakub)
* В противном случае рассмотрим более общие решения, среди которых я бы рекомендовал вам взглянуть на [DB.UP](http://dbup.github.io) и если код python подходит для вас, проверьте [Alembic](https://alembic.readthedocs.org/en/latest/), он позволяет вам писать свои миграции с помощью действительно приятного API-интерфейса python.
* если ничего не работает для вас, создайте моментальный снимок текущей схемы db и начните выполнять дифференциальные скрипты, которые вы можете использовать с помощью собственного инструмента или DB.UP

 

Я не уверен, может ли это помочь, но кто знает.

Итак, есть ли способ восстановить базу данных сервера в локальной среде? Если да, вы можете попытаться присоединиться к системным представлениям для каждой базы данных и сравнить их?

Я предлагаю что-то вроде этого (было быстрым решением, поэтому, пожалуйста, извините за форматирование и другие распространенные вещи).

```
USE [master]
GO

SELECT
    LocalDataBaseTable.name AS TableName,
    LocalDataBaseTableColumns.name AS [Column],
    LocalDataBaseTypes.name AS DataType,
    LocalDataBaseTableColumns.max_length,
    LocalDataBaseTableColumns.[precision]
INTO #tmpLocalInfo
FROM LocalTable.sys.columns as LocalDataBaseTableColumns 
INNER JOIN LocalTable.sys.tables AS LocalDataBaseTable
    ON LocalDataBaseTableColumns.object_id = LocalDataBaseTable.object_id
INNER JOIN LocalTable.sys.types AS LocalDataBaseTypes
    ON LocalDataBaseTypes.user_type_id = LocalDataBaseTableColumns.user_type_id

SELECT 
    ServerDataBaseTable.name AS TableName,
    ServerDataBaseTableColumns.name AS [Column],
    ServerDataBaseTypes.name AS DataType,
    ServerDataBaseTableColumns.max_length,
    ServerDataBaseTableColumns.[precision]
INTO #tmpServerInfo
FROM ServerTable.sys.columns as ServerDataBaseTableColumns 
INNER JOIN ServerTable.sys.tables AS ServerDataBaseTable
    ON ServerDataBaseTableColumns.object_id = ServerDataBaseTable.object_id
INNER JOIN ServerTable.sys.types AS ServerDataBaseTypes
    ON ServerDataBaseTypes.user_type_id = ServerDataBaseTableColumns.user_type_id

SELECT
    #tmpServerInfo.* 
FROM #tmpLocalInfo 
RIGHT OUTER JOIN #tmpServerInfo 
    ON #tmpLocalInfo.TableName = #tmpServerInfo.TableName COLLATE DATABASE_DEFAULT
    AND #tmpLocalInfo.[Column] = #tmpServerInfo.[Column]  COLLATE DATABASE_DEFAULT
WHERE #tmpLocalInfo.[Column] IS NULL

DROP TABLE #tmpLocalInfo
DROP TABLE #tmpServerInfo

```

Это вернет всю информацию о пропущенных столбцах в вашей локальной базе данных. Идея состоит в том, чтобы исследовать представления `'sys'` и выяснить, есть ли у вас подходящее решение.

 

Вы можете использовать [этот простой script](https://github.com/dlevsha/compalex), который показывает различия между таблицами, представлениями, индексами и т.д.

>  Compalex - это бесплатный легкий script для сравнения двух схем базы данных. Это поддерживает MySQL, MS SQL Server и PostgreSQL.
> 
> 
> 

или посмотрите на этот вопрос [Сравните две базы данных MySQL](/questions/17566/compare-two-mysql-databases). Этот вопрос о сравнении двух схем MySQL, но некоторые из перечисленных инструментов поддерживают MSSQL или имеют версию MSSQL (например <http://www.liquibase.org/>).

Другой ответ [Какой лучший инструмент для сравнения двух баз данных SQL Server (схема и данные)?](/questions/22942/what-is-best-tool-to-compare-two-sql-server-databases-schema-and-data)