Jak mogę porównać schemat dwóch baz danych?

Czy istnieje sposób na znalezienie różnic w dwóch bazach danych SQL Server (tylko schemat). Jeden jest lokalny, a drugi znajduje się na stronie klienta. Występują problemy z uruchomieniem niektórych raportów przez raporty kryształowe, a także z niewykonywaniem niektórych kodów i wygląda na to, że schematy się nie zgadzają.

Czy mogę uruchomić to samo polecenie w obu bazach danych i porównać wyniki, aby stwierdzić, gdzie są różnice?

Jeśli nie możesz użyć jednego z wielu dostępnych narzędzi z powodu problemów z łącznością i chcesz porównać „offline”, możesz użyć SSMS do wygenerowania skryptów dla wszystkich obiektów bazy danych, klikając bazę danych prawym przyciskiem myszy i używając „Zadania ... / Generuj” Skrypty ”i upewnij się, że chcesz utworzyć jeden plik na obiekt.

Gdy już to zrobisz dla obu baz danych, przenieś dwa zestawy skryptów na lokalną maszynę w dwóch osobnych folderach i użyj WinMerge (lub podobnego), aby porównać oba.

Inną opcją jest użycie SQL Server Data Tools (SSDT), rozszerzenia Visual Studio. Możesz wyodrębnić schemat bazy danych jako plik .dacpac i porównać go z innym plikiem .dacpac lub istniejącą bazą danych. Narzędzie SSDT jest dołączone do narzędzi klienta SQL Server 2012, dzięki czemu jest dość dostępne. Pełne instrukcje na temat uruchamiania porównania można znaleźć na stronie MSDN .

Po zmaganiu się z łatwym sposobem wykonania tego samego zadania - zobacz, co się zmieniło między dwoma modelami, napisałem następujący skrypt SQL, który porówna dwa schematy w celu ustalenia nowych i usuniętych kolumn

set nocount on;
-- Set the two variables newmodel and oldmodel to the appropriate database names and execute the script

declare @newmodel varchar(50), @oldmodel varchar(50);

Set @newmodel = '[NewModel to Compare]';
set @oldmodel = '[OldModel to Compare]';


Declare @Temp table (TABLE_SCHEMA varchar(40), TABLE_NAME varchar(40), COLUMN_NAME varchar(50), ORDINAL_POSITION int, IS_NULLABLE varchar(5), NullChange varchar(5), Comment varchar(50));

Declare @script varchar(5000);


set @script = '
Select nc.TABLE_SCHEMA, nc.TABLE_NAME, nc.COLUMN_NAME, nc.ORDINAL_POSITION, nc.IS_NULLABLE, IIF(nc.IS_NULLABLE <> oc.IS_NULLABLE, ''Yes'', ''No''), 
        IIF(oc.COLUMN_NAME IS NULL, convert(varchar(20), ''ADDED COLUMN''), convert(varchar(20), ''--'')) as Comment
    from {NEW}.INFORMATION_SCHEMA.COLUMNS nc
        LEFT join {OLD}.INFORMATION_SCHEMA.COLUMNS oc 
            on nc.TABLE_NAME = oc.TABLE_NAME and nc.COLUMN_NAME = oc.COLUMN_NAME
UNION ALL
    Select oc.TABLE_SCHEMA, oc.TABLE_NAME, oc.COLUMN_NAME, oc.ORDINAL_POSITION, oc.IS_NULLABLE, ''No'', ''DELETED COLUMN'' as Comment
    from {OLD}.INFORMATION_SCHEMA.COLUMNS oc
    where CONCAT(oc.TABLE_NAME, ''.'', oc.COLUMN_NAME) 
        not in (Select CONCAT(TABLE_NAME, ''.'', COLUMN_NAME) from {NEW}.INFORMATION_SCHEMA.COLUMNS)
';


Set @script = replace(@script, '{OLD}', @oldmodel);
Set @script = replace(@script, '{NEW}', @newmodel);

--print @script

Insert into @Temp
    exec(@script);

Select * from @Temp where Comment <> '--'
order by TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME;
go

Jeśli chcesz porównać więcej niż jeden plik bazy danych, możesz wykonać skrypt SQLPackage.exe .

Nie mam dla ciebie działającego kodu, ale możesz zajrzeć do dokumentacji SQLPackage.exe .

Wyodrębnij swoją główną bazę danych do pliku dacpac, a następnie porównaj plik dacpac z resztą baz danych. Wynikiem porównania może być raport xml zmian lub plik .sql, który można uruchomić w celu synchronizacji baz danych.

Coś takiego:

sqlpackage.exe /a:Extract /scs:Server=MyLaptopSQL2014;Database=Test; /tf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac  

i wtedy

sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test1 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest1.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test2 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest2.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 

Można przyjrzeć się tym artykułem lub ten jeden dla przykładowego kodu.

Wyszukaj hasło „SQL Server Compare”, a znajdziesz wiele narzędzi. W mojej pracy używamy Red Gate SQLCompare . Ma 14-dniowy okres próbny. Ale skoro mówisz o dwóch różnych środowiskach, nie sądzę, aby to działało dla ciebie, chyba że klient wyśle ​​ci kopię zapasową swojej bazy danych. Inną opcją jest pisanie zapytań względem tabel systemowych (takich jak sys.indexes, sys.tables itp.).

Najłatwiejszym sposobem jest skorzystanie z automatycznego narzędzia zbudowanego w tym celu , ale jeśli nie masz do niego dostępu, możesz uzyskać wszystkie podstawowe informacje, których potrzebujeszINFORMATION_SCHEMA widoków.

Korzystanie z metadanych w INFORMATION_SCHEMA jest prawdopodobnie łatwiejszą opcją niż generowanie skryptów DDL i porównywanie źródeł, ponieważ masz znacznie większą kontrolę nad sposobem prezentacji danych. Nie można tak naprawdę kontrolować kolejności, w jakiej generowane skrypty prezentują obiekty w bazie danych. Ponadto skrypty zawierają fragment tekstu, który może być domyślnie zależny od implementacji i może powodować wiele „szumów” niezgodności, gdy prawdopodobnie naprawdę trzeba się skupić na brakującej tabeli, widoku lub kolumnie lub ewentualnie typie danych kolumny lub niedopasowanie wielkości.

Napisz zapytanie (lub zapytania), aby uzyskać informacje istotne dla twojego kodu z INFORMATION_SCHEMAwidoków i uruchomić je na każdym serwerze SQL z SSMS. Następnie możesz zrzucić wyniki do pliku i użyć narzędzia do porównywania plików tekstowych (nawet MS Word) lub zrzucić wyniki do tabel i uruchomić zapytania SQL, aby znaleźć niezgodności.

Podaję tę odpowiedź ze względu na nowe pytanie, które oznaczono jako duplikat.

Kiedyś musiałem porównać dwie produkcyjne bazy danych i znaleźć różnice między nimi w schemacie. Jedynymi interesującymi przedmiotami były tabele, które zostały dodane lub upuszczone oraz kolumny, które zostały dodane, usunięte lub zmienione. Nie mam już skryptów SQL, które opracowałem, ale następująca jest ogólna strategia. Baza danych nie była SQL Server, ale myślę, że stosuje się tę samą strategię.

Najpierw stworzyłem coś, co najlepiej można opisać jako metadatasę. Tabele użytkowników tej bazy danych zawierały opisy danych skopiowane z tabel systemowych produkcyjnych baz danych. Rzeczy takie jak nazwa tabeli, nazwa kolumny, typ danych i precyzja. Był jeszcze jeden element, nazwa bazy danych, który nie istniał w żadnej z produkcyjnych baz danych.

Następnie opracowałem skrypty, które połączyły selekcje z tabel systemowych produkcyjnych baz danych z wstawkami do tabel użytkowników metadatazy.

Na koniec opracowałem zapytania, aby znaleźć tabele, które istniały w jednej bazie danych, ale nie w drugiej, oraz kolumny z tabel w obu bazach danych, które były tylko w jednej bazie danych, oraz kolumny o niespójnych definicjach między dwiema bazami danych.

Spośród około 100 tabel i 600 kolumn znalazłem garść niespójności i jedną kolumnę, która została zdefiniowana jako zmiennoprzecinkowa w jednej bazie danych i liczba całkowita w drugiej. Ten ostatni okazał się wybawieniem, ponieważ odkrył problem, który nękał jedną z baz danych od lat.

Model metadatazy został zasugerowany przez odpowiednie tabele systemowe. Kwerendy nie były trudne do zbudowania, obracały się głównie wokół grupy i miały liczbę (nazwa bazy danych) = 1.

W twoim przypadku, przy 700 produkcyjnych bazach danych, możesz zautomatyzować dwa pierwsze kroki bardziej niż ja przy użyciu tylko dwóch baz danych do porównania. Ale pomysł jest podobny.

Miałem dokładnie to samo pytanie i uważam, że Microsoft SQL Server Management Studio (SSMS) ma o wiele łatwiejsze / prostsze rozwiązanie niż wszystko, co tu widziałem. Mam witrynę produkcyjną z MS SQL Server Express i już wkrótce będzie ich więcej, gdzie nie chcę instalować VisualStudio ani innych aplikacji innych niż SSMS.

Tak więc w SSMS kliknij bazę danych prawym przyciskiem myszy, aby uzyskać schemat. Wybierz Zadania> Generuj skrypty ... aby otworzyć kreatora do skryptu schematu i konfiguracji dla całej bazy danych (lub wybranych obiektów, jeśli chcesz). Zachowałem wszystkie domyślne opcje oprócz ścieżki / nazwy pliku, ale narzędzie ma mnóstwo opcji. Kreator utworzył jeden kod SQL, który skopiowałem przez OneDrive z powrotem na komputer. Następnie użyłem Notepad ++, aby porównać SQL z plikiem wygenerowanym w ten sam sposób w mojej bazie danych SIT. Musisz odfiltrować trafienia od daty / godziny w komentarzach, ale w przeciwnym razie jest to świetne porównanie dwóch baz danych.

Presto! Zapisanie tego było znacznie trudniejsze niż faktyczne porównanie.

Świetnym narzędziem, którego używam (choć nie jest aktualizowany od jakiegoś czasu, nadal działa) jest AdeptSqlDiff

Czy zarówno schemat porównuje, jak i porównania danych. Podobnie jak RedGate, istnieje koszt, ale także 30-dniowy okres próbny. A cena jest całkiem rozsądna.

Może ten darmowy skrypt https://github.com/dlevsha/compalex może ci pomóc. Obsługuje Microsoft SQL Server.

Compalex to darmowy, lekki skrypt do porównywania dwóch schematów baz danych. Obsługuje MySQL, MS SQL Server i PostgreSQL.

Możesz wypróbować wersję demo tutaj

http://demo.compalex.net/

Istnieje wiele narzędzi innych firm, które wykonują porównywanie schematów i danych oraz synchronizację. Dwa narzędzia, których możesz użyć, to te, które opracowałem wraz z moim zespołem, xSQL Schema Compare do porównań schematów i xSQL Data Compare do porównań danych między obiektami o tym samym schemacie. Mam nadzieję że to pomoże!
Oświadczenie: Jestem związany z xSQL

Na rynku dostępnych jest wiele narzędzi, których możesz użyć do wykonania zadania. Moja firma używa ApexSQL Diff zarówno do porównywania, jak i synchronizacji, ponieważ jest ona bezpłatna dla platformy Azure, ale nie można się pomylić z narzędziami Devart lub Redgate.

Jestem fanem SQL DBDiff , który jest narzędziem typu open source, którego możesz używać do porównywania tabel, widoków, funkcji, użytkowników itp. Dwóch instancji baz danych SQL Server i generowania skryptu zmiany między źródłową i docelową bazą danych.

Zrobiłem narzędzie MssqlMerge , które pozwala porównywać bazy danych MSSQL, zarówno strukturę, jak i dane. Dostępna jest darmowa wersja, która pozwala porównać definicje tabel, widoki, procedury składowane i funkcje. Dostępna jest również wersja Pro, która obsługuje więcej typów obiektów i ma funkcję „Różnicowanie wyników zapytania”, w której można uruchamiać i porównywać dowolne wyniki zapytań, w tym zapytania z widokami systemu, aby porównać niektóre inne szczegóły niedostępne po wyjęciu z pudełka.

Sprawdź to:

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
       COLUMN_DEFAULT ,
       DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH ,
       NUMERIC_PRECISION ,
       NUMERIC_PRECISION_RADIX ,
       NUMERIC_SCALE ,
       DATETIME_PRECISION
FROM   INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA in ('dbo','meta')
and table_name in (select name from sys.tables)
order by TABLE_SCHEMA ,       TABLE_NAME ,ORDINAL_POSITION

Korzystam z tego bezpłatnego (i open source) narzędzia: OpenDBDiff

DBDiff jest najlepszym narzędziem do tego, można go znaleźć tutaj .