W poprzedniej części (patrz tutaj) zajmowaliśmy się wykrywaniem różnic w schematach. Dzisiaj zajmiemy się czymś bardziej popularnym – będziemy szukać różnic w tabelach.
Podobnie jak poprzednio będziemy działać w obrębie dwóch utworzonych baz o nazwach poligon oraz poligon_dev. Utwórzmy sobie kilka zatem kilka tabel w obu bazach.
use poligon; create table dbo.uzytkownicy ( idUzytkownik int identity(1, 1) primary key clustered, imie nvarchar(100) not null, nazwisko nvarchar(200) not null, email varchar(200) not null ); create table dbo.uzytkownicy_uprawnienia ( idUprawnienia int identity(1, 1) primary key clustered, idUzytkownik int constraint FK_Uprawnienia_Uzytkownik foreign key references dbo.uzytkownicy (idUzytkownik), zasob sysname not null, poziom int not null default(0) ); go use poligon_dev; create table dbo.uzytkownicy ( idUzytkownik int identity(1, 1) primary key clustered, imie nvarchar(100) not null, nazwisko nvarchar(200) not null, email varchar(200) not null, data_utworzenia datetime2 default(getdate()) ); create table dbo.uzytkownicy_uprawnienia ( idUprawnienia int identity(1, 1) primary key clustered, idUzytkownik int constraint FK_Uprawnienia_Uzytkownik foreign key references dbo.uzytkownicy (idUzytkownik), zasob sysname not null, poziom int not null default(0), data_utworzenia datetime2 default(getdate()) ); create table dbo.uzytkownicy_adresy ( idAdres int identity(1, 1) primary key clustered, idUzytkownik int constraint FK_Adresy_Uzytkownik foreign key references dbo.uzytkownicy (idUzytkownik), miejscowosc nvarchar(200) not null, kod_pocztowy char(6) not null, ulica nvarchar(200) null, nr_domu nvarchar(10) not null, nr_lokalu nvarchar(5) null );
Jak widać w bazie poligon_dev pojawiły się dodatkowe pola w tabelach oraz jedna nowa tabela. Spróbujmy teraz poprosić w jakiś sposób serwer SQL aby podał nam precyzyjną informację odnośnie wspomnianych różnic.
Warto od razu zauważyć, iż do rozwiązania mamy tak na prawdę dwa problemy. Pierwszym jest znalezienie różnic w obecności samych tabel, drugim natomiast jest porównanie pól w istniejących już tabelach.
Po podstawowe informacje odnośnie samych tabel możemy zgłosić się bardzo prostymi zapytaniami, które wyglądają następująco:
select * from poligon.sys.tables select * from poligon_dev.sys.tables
Odpowiedź na te zapytania wygląda w moim przypadku tak jak poniżej (każdy z wierszy ma bardzo wiele pól, aby niepotrzebnie nie zaciemniać obrazu prezentuję tylko fragment zrzutu ekranu zawierające najważniejsze dla nas informacje).
To co nas będzie interesować najbardziej z uzyskanych wyżej informacji to pola name, object_id oraz schema_id. Uczyńmy jednak nasze zapytania bardziej precyzyjnymi i przetłumaczmy od razu identyfikator schematu na jego nazwę.
select i.CATALOG_NAME as dbname, t.name as [table], t.object_id, s.name as [schema] from poligon.sys.tables as t inner join poligon.sys.schemas as s on s.schema_id = t.schema_id inner join poligon.INFORMATION_SCHEMA.SCHEMATA as i on i.SCHEMA_NAME = s.name select i.CATALOG_NAME as dbname, t.name as [table], t.object_id, s.name as [schema] from poligon_dev.sys.tables as t inner join poligon_dev.sys.schemas as s on s.schema_id = t.schema_id inner join poligon_dev.INFORMATION_SCHEMA.SCHEMATA as i on i.SCHEMA_NAME = s.name
Wynikiem powyższych zapytań będzie następujący widok. Jak widać uzupełniłem od razu informację odnośnie nazwy bazy danych (robię to tylko dla celów prezentacji danych – ostatecznie nazwy te nie będą nam potrzebne).
Informacje na temat pól tabeli pozyskujemy odpytując np. widok systemowy sys.columns (możemy też użyć np. widoku sys.all_columns), gdzie object_id odpowiada identyfikatorowi obiektu tabeli.
UWAGA – kolumny nie posiadają własnego identyfikatora object_id.
Zamiast tego posługują się identyfikatorem obiektu tabeli oraz osobnym identyfikatorem column_id, który jest unikalny w ramach tylko konkretnej tabeli. Aby pokazać o co chodzi posłużę się zapytaniem tylko dla bazy poligon (dla wygody odwołam się do konkretnych object_id tabel w tej bazie).
select * from poligon.sys.columns as c where c.object_id in (1941581955, 1973582069)
W efekcie uzyskamy mniej więcej następujący widok (liczba pól jest tutaj również spora, więc ograniczę się do zrzutu ekranu tylko najważniejszej części uzyskanego rezultatu).
Ciekawą informacją z uzyskanego wyniku jest informacja o typie pola oraz jego rozmiarze. Warto od razu zwrócić uwagę, iż rozmiar to nie zawsze jest ta sama wartość, którą wpisaliśmy deklarując pole. Jest to bowiem rozmiar pola podany w bajtach (co łatwo zauważyć porównując rozmiary pół varchar i nvarchar – drugi typ zapisuje jeden znak przy pomocy dwóch bajtów a nie jednego jak w przypadku pierwszego z nich). Dla celów porównawczych wyliczanie wartości nie ma jednak znaczenia – jeśli pola w obu porównywanych tabelach są tego samego typu ale o różnym rozmiarze, to już mamy wystarczającą sygnalizację różnicy.
Wspomnę jeszcze o jednej rzeczy, która niekoniecznie może być oczywista. Porównując obiekty pomiędzy dwiema różnymi bazami nie możemy posługiwać się ich identyfikatorami. Gdybym np. teraz utworzył bazy wraz z obiektami na nowo to uzyskiwane wyżej wartości identyfikatorów mogłyby być już inne – dlatego do porównania musimy tłumaczyć te identyfikatory na nazwy i dopiero te ze sobą porównywać.
Czy można połączyć w jakiś sposób oba opisane wyżej problemy w jeden? Mam na myśli zrobienie jednego zestawienia, które pokazywałoby zarówno różnice w tabelach jak i w polach. Odpowiedzią na to pytanie będzie uzmysłowienie sobie czym tak na prawdę jest tabela. Otóż tabela jest to nazwany zbiór pól, co oznacza, że aby utworzyć tabelę konieczne jest utworzenie co najmniej jednego pola. Możemy więc połączyć oba problemy wyłącznie do zestawienia samych pól – jeśli w którejś z baz jakaś tabela nie istnieje to nie istnieją również jej pola. Dlatego też w dalszych rozważaniach połączę zapytania dotyczące tabel wraz z tymi związanymi z polami.
select i.CATALOG_NAME as dbname, t.name as [table], s.name as [schema], c.name as [column], tp.name as [typ], c.max_length from poligon.sys.tables as t inner join poligon.sys.schemas as s on s.schema_id = t.schema_id inner join poligon.INFORMATION_SCHEMA.SCHEMATA as i on i.SCHEMA_NAME = s.name inner join poligon.sys.columns as c on c.object_id = t.object_id inner join poligon.sys.types as tp on tp.user_type_id = c.user_type_id select i.CATALOG_NAME as dbname, t.name as [table], s.name as [schema], c.name as [column], tp.name as [typ], c.max_length from poligon_dev.sys.tables as t inner join poligon_dev.sys.schemas as s on s.schema_id = t.schema_id inner join poligon_dev.INFORMATION_SCHEMA.SCHEMATA as i on i.SCHEMA_NAME = s.name inner join poligon_dev.sys.columns as c on c.object_id = t.object_id inner join poligon_dev.sys.types as tp on tp.user_type_id = c.user_type_id
Powyższe zapytania zwrócą nam następujący rezultat.
Teraz wystarczy to ze sobą zestawić i porównać. W odróżnieniu jednak od zapytań, którymi posługiwaliśmy się w przypadku poszukiwania różnic w schematach, tutaj musimy posłużyć się bardziej złożonym podejściem. Konkretnie posłużymy się operatorem except, który zwraca te rekordy z pierwszego zapytania, których nie ma w drugim zapytaniu. Dla wygody powyższe zapytania ujmiemy w bloki CTE. Musimy również pamiętać, że porównanie będzie należało wykonać w dwóch etapach – w pierwszym zwrócimy pola istniejące w bazie poligon a nieistniejące w bazie poligon_dev, w drugim natomiast odwrotnie – zwrócimy pola istniejące w bazie poligon_dev a nieistniejące w bazie poligon. Na końcu złączymy wyniki obu takich zapytań.
with baza1 as ( select i.CATALOG_NAME as dbname, t.name as [table], s.name as [schema], c.name as [column], tp.name as [typ], c.max_length from poligon.sys.tables as t inner join poligon.sys.schemas as s on s.schema_id = t.schema_id inner join poligon.INFORMATION_SCHEMA.SCHEMATA as i on i.SCHEMA_NAME = s.name inner join poligon.sys.columns as c on c.object_id = t.object_id inner join poligon.sys.types as tp on tp.user_type_id = c.user_type_id ), baza2 as ( select i.CATALOG_NAME as dbname, t.name as [table], s.name as [schema], c.name as [column], tp.name as [typ], c.max_length from poligon_dev.sys.tables as t inner join poligon_dev.sys.schemas as s on s.schema_id = t.schema_id inner join poligon_dev.INFORMATION_SCHEMA.SCHEMATA as i on i.SCHEMA_NAME = s.name inner join poligon_dev.sys.columns as c on c.object_id = t.object_id inner join poligon_dev.sys.types as tp on tp.user_type_id = c.user_type_id ), cmp_baza1 as ( select [table], [schema], [column], [typ], [max_length] from baza1 except select [table], [schema], [column], [typ], [max_length] from baza2 ), cmp_baza2 as ( select [table], [schema], [column], [typ], [max_length] from baza2 except select [table], [schema], [column], [typ], [max_length] from baza1 ) select (select top 1 dbname from baza1 group by dbname) as dbname, * from cmp_baza1 union all select (select top 1 dbname from baza2 group by dbname) as dbname, * from cmp_baza2
Wynikiem takiego zapytania będzie precyzyjna lista pól, które istnieją w jednej bazie a brakuje ich w drugiej.
Jak widać nie dostaliśmy żadnego wyniku z bazy poligon. Nie powinno to jednak dziwić ponieważ wszystkie nowe elementy pojawiły się faktycznie tylko w bazie poligon_dev. Zobaczmy jednak co się stanie kiedy wprowadzimy w bazie poligon kilka zmian.
alter table poligon.dbo.uzytkownicy alter column email varchar(250) not null; alter table poligon.dbo.uzytkownicy_uprawnienia alter column zasob nvarchar(50) not null;
Po takich zmianach (w jednym przypadku zmieniliśmy rozmiar pola a w drugim typ oraz rozmiar) wykonanie zapytania porównującego dostarczy nam następujący wynik.
Przy małej liczbie różnic można dosyć łatwo zauważyć, że dwa pola istnieją w obu bazach i różnią się tylko definicją. Kiedy różnic będzie jednak więcej porównywanie wyników może już nie być jednak takie proste. Zestawienie wyników obok siebie (w ostatnim kroku zamiast złączenia union all można by wykorzystać np. full outer join) również może nie być zbyt czytelne. Chyba, że skorzystamy z faktu, iż element definiujący nazwę pola w obu bazach jest taki sam i składa się z nazwy schematu, nazwy tabeli oraz nazwy samego pola. W takiej sytuacji jedyną różnicą będzie tylko typ i rozmiar pola, przy czym wartości NULL będą oznaczać brak pola w jednej z baz. Zmodyfikujmy zatem nasze zapytanie. Ponieważ nie będziemy już potrzebować informacji o bazie, dlatego nasze zapytanie może się dodatkowo uprościć.
with baza1 as ( select t.name as [table], s.name as [schema], c.name as [column], tp.name as [typ], c.max_length from poligon.sys.tables as t inner join poligon.sys.schemas as s on s.schema_id = t.schema_id inner join poligon.sys.columns as c on c.object_id = t.object_id inner join poligon.sys.types as tp on tp.user_type_id = c.user_type_id ), baza2 as ( select t.name as [table], s.name as [schema], c.name as [column], tp.name as [typ], c.max_length from poligon_dev.sys.tables as t inner join poligon_dev.sys.schemas as s on s.schema_id = t.schema_id inner join poligon_dev.sys.columns as c on c.object_id = t.object_id inner join poligon_dev.sys.types as tp on tp.user_type_id = c.user_type_id ), cmp_baza1 as ( select * from baza1 except select * from baza2 ), cmp_baza2 as ( select * from baza2 except select * from baza1 ) select coalesce(b1.[schema], b2.[schema]) as [schema], coalesce(b1.[table], b2.[table]) as [table], coalesce(b1.[column], b2.[column]) as [column], b1.typ as b1_typ, b1.max_length as b1_length, b2.typ as b2_typ, b2.max_length as b2_length from cmp_baza1 as b1 full outer join cmp_baza2 as b2 on b1.[schema] = b2.[schema] and b1.[table] = b2.[table] and b1.[column] = b2.[column]
Teraz wiedząc, że pierwszą bazą jest poligon a drugą poligon_dev możemy w czytelny sposób odczytać różnice pomiędzy tabelami.
Na koniec, podobnie jak we wpisie o poszukiwaniu różnic w schematach, możemy opakować sobie powyższe zapytanie w wygodną procedurę, którą można również umieścić w bazie master. Procedura może nazywać się np. dbo.migracje_sprawdzTabele i być parametryzowana nazwami porównywanych baz danych. Skrypt tworzący taką procedurę, wraz z objaśnieniami, zamieściłem w repozytorium na GitHubie. Wywołanie takiej procedury będzie bardzo proste – w naszym przypadku mogłoby wyglądać ono jak poniżej.
-- Należy uważać na kolejność podawania nazw baz danych exec master.dbo.migracje_sprawdzTabele 'poligon', 'poligon_dev';
W kolejnym wpisie z tego cyklu zajmiemy się tematem różnic w indeksach, kluczach i ograniczeniach. Zapraszam do lektury 🙂
