W poprzednim wpisie (patrz tutaj) omawiałem wykrywanie różnic w tabelach oraz ich polach. Jak jednak wiadomo z tabelami związanych jest trochę więcej różnych obiektów. W dzisiejszej części zajmiemy się wyszukiwaniem różnic w kluczach, indeksach oraz ograniczeniach.

Do pracy wykorzystamy te same tabele w bazach poligon oraz poligon_dev, które utworzyliśmy poprzednim razem. Dla wygody zamieszczę tutaj definicje tych tabel (z uwzględnieniem zmian jakie wykonane były po drodze).

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(250) 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 nvarchar(50) 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
);

Kiedy przyjrzymy się tym tabelom od razu zauważymy utworzone klucze oraz ograniczenia. Aby jednak danych do analizy było nieco więcej dodajmy kilka dodatkowych indeksów w bazie poligon_dev (zwykle właśnie z taką sytuacją mamy do czynienia – robimy coś w bazie developerskiej a potem chcemy mieć pewność, że odzwierciedliliśmy wszystkie zmiany w bazie produkcyjnej).

use poligon_dev;

alter table dbo.uzytkownicy add constraint UQ_uzytkownik_email unique (email);
alter table dbo.uzytkownicy_uprawnienia add constraint UQ_uzytkownik_uprawnienia unique (idUzytkownik, zasob);
alter table dbo.uzytkownicy_uprawnienia add constraint CK_uzytkownik_uprawnienia_poziom check (poziom > 0 and poziom < 99);

Jak widać zmiany wydają się takie w miarę sensowne. Chcemy aby adres email był unikalny oraz aby poziom uprawnień zawierał się w zakresie od 0 do 99 oraz aby dla każdego użytkownika i zasobu istniał tylko jeden unikalny rekord.

Badanie różnic dotyczących analizowanych przez nas obecnie obiektów jest według mnie najbardziej skomplikowanym procesem. Różne typy ograniczeń operują na skrajnie różnych metodach zapisu swoich definicji. Sprawia to, iż istnieje bardzo wiele różnych widoków systemowych – każdy dedykowany innemu rodzajowi ograniczenia. Bardzo łatwo rozpoznać je po nazwach – zaliczają się do nich np. takie widoki jak sys.key_constraints, sys.foreign_keys, sys.check_constraints, sys.default_constraints i inne. 

Konieczność wykorzystania praktycznie wszystkich takich widoków sprawi, że omawiane przeze mnie zagadnienie będzie wiązało się na napisaniem bardzo rozbudownego zapytania (postaramy się napisać całość w formie jednego zapytania SQL). 

Na początek sugerują posłużyć się widokami bardziej ogólnymi jak sys.sysconstraints czy wręcz sys.objects (każdy z indeksów, kluczy czy ograniczeń jest oddzielnym obiektem, więc jak najbardziej można się go spodziewać w widoku sys.objects). Pozwolą nam one pozyskać ogólną listę ograniczeń, do której będzie stopniowo dodawać kolejne informacje.

Ponieważ w widoku sys.objects musielibyśmy dosyć precyzyjnie wymienić wszystkie typy poszukiwanych obiektów dlatego uważam, że w naszym przypadku najlepszą i najłatwiejszą metodą będzie połączenie obu widoków (sys.sysconstraints oraz sys.objects) przy czym widok sys.sysconstraints posłuży nam po prostu jako filtr (widok ten nie zawiera zbyt wielu informacji – nie ma w nim nawet nazw obiektów, posiada za to wiedzę odnośnie identyfikatorów obiektów).

use poligon_dev;

select o.*
from sys.sysconstraints as c
inner join sys.objects as o on o.object_id = c.constid

Powyższe zapytanie zwróci nam w efekcie następującą kolekcję wyników (dla lepszej czytelności wykonałem zrzut ekranu tylko fragmentu kolumn).

Jak łatwo się domyślić w kolumnie parent_object_id powinniśmy się spodziewać obiektu tabeli z jaką powiązany jest dany obiekt. Rozszerzmy zatem nasze zapytanie w podobny sposób jak robiliśmy to w przypadku tabel.

use poligon_dev;

select s.name as [schema], t.name as [table], o.name as [constraint], o.type, o.type_desc
from sys.sysconstraints as c
inner join sys.objects as o on o.object_id = constid
inner join sys.objects as t on t.object_id = o.parent_object_id
inner join sys.schemas as s on s.schema_id = t.schema_id

Efektem takiego zapytania będzie poniższy widok.

Czy czegoś nam tutaj brakuje? Na pierwszy rzut oka pewnie nie ale po chwili namysłu zauważymy, że przecież przy ograniczeniu check nie mamy żadnej informacji odnośnie definicji tego ograniczenia. Mamy ograniczenia typu default ale nie wiemy jaka wartość będzie tą domyślną oraz jakiej kolumny ono w ogóle dotyczy. Podobnie w ograniczeniach foreign key - wiemy, że są ale nie wiemy o nich nic więcej. Na pewno chcielibyśmy też wiedzieć czy klucze główne są klastrowe czy też nie. W tym momencie musimy skorzystać z pomocy wspomnianych wcześniej widoków dedykowanych konkretnym typom. Jeśli ktoś jest ciekawy jakich informacji dostarczają te widoki to polecam wykonać np. następujące zapytanie (nie będę już zamieszczał tutaj wyniku).

use poligon_dev;

select * from sys.indexes;
select * from sys.check_constraints;
select * from sys.foreign_keys;
select * from sys.default_constraints;

W przypadku widoku sys.indexes trzeba uważać – object_id wskazuje na tabelę a nie obiekt indeksu! Konieczne jest w tym przypadku posłużenie się nazwą obiektu (jest ona unikalna w bazie).

Za wyjątkiem kluczy obcych, w pozostałych dwóch przypadkach potrzebne nam informacje są podane niemal na tacy w kolumnie o nazwie definition. W przypadku kluczy obcych dobrze będzie moim zdaniem zbudować sobie osobne CTE, w którym przygotujemy opis referencji jako definicję. Takie zapytanie mogłoby wyglądać następująco.

use poligon_dev;

select fk.[object_id], concat(s.name, '.', ro.name, '(', c.name, ')') as [definition]
from sys.foreign_keys as fk
inner join sys.objects as ro on ro.object_id = fk.referenced_object_id
inner join sys.schemas as s on s.schema_id = ro.schema_id
inner join sys.columns as c on c.object_id = ro.object_id and c.column_id = fk.key_index_id

Jak widać definicję referencji zbudowałem tutaj z połączenia nazw schematu, tabeli oraz pola. W naszym przypadku taka definicja w zupełności powinna wystarczyć. 

Podobny problem musimy rozwiązać jeszcze dla ograniczeń domyślnych. Sama definicja wartości to za mało – powinniśmy jeszcze zidentyfikować kolumnę, której to ograniczenie dotyczy. Zapytanie jakie chciałbym tutaj zaproponować będzie dosyć proste.

use poligon_dev;

select dc.object_id, concat('[', c.name, ']=', dc.definition) as [definition]
from sys.default_constraints as dc
inner join sys.columns as c on c.object_id = dc.parent_object_id and c.column_id = dc.parent_column_id

Ograniczyłem się jak widać wyłącznie do zaznaczanie, która kolumna jaką wartość ma przyjąć. Teraz zajmijmy się jednym z trudniejszych zapytań – w ograniczeniach unikalnych (od razu dodajmy, że klucze podstawowe również są unikalne i jak najbardziej możemy je tutaj uwzględnić) musimy odtworzyć kolekcję wszystkich pól biorących udział w definicji (przypomnijmy sobie, że ograniczenie UQ_uzytkownik_uprawnienia odwołuje się właśnie do dwóch pól w tabeli uprawnień użytkowników). Ponieważ kolekcja tych pól zawsze odnosi się się do tej samej tabeli co ograniczenie, więc najlepszym miejscem na znalezienie powiązań będzie użycie widoku INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE. Aby zserializować nazwy kolumn do jednego pola posłużymy się jedną z metod opisanych w innym wpisie na tym blogu (patrz tutaj).

use poligon_dev;

select k.object_id, stuff(( select concat(', [', COLUMN_NAME, ']')
							from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
							where CONSTRAINT_NAME = k.name
							order by COLUMN_NAME asc
							for xml path('')), 1, 2, '') as [definition]
from sys.sysconstraints as c
inner join sys.key_constraints as k on k.object_id = c.constid and k.type_desc in ('UNIQUE_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')

Ważne jest pamiętanie o posortowaniu nazw kolumn. Bez tego istniałoby ryzyko, że kolumny ułożyłyby się w przypadkowej kolejność, przez co porównanie definicji mogłoby pokazać fałszywą różnicę. 

Zapiszmy teraz nasze zapytanie z uwzględnieniem dodatkowych informacji, które pozyskaliśmy wyżej.

use poligon_dev;

with fk as (
	select fk.[object_id], concat(s.name, '.', ro.name, '(', c.name, ')') as [definition]
	from sys.foreign_keys as fk
	inner join sys.objects as ro on ro.object_id = fk.referenced_object_id
	inner join sys.schemas as s on s.schema_id = ro.schema_id
	inner join sys.columns as c on c.object_id = ro.object_id and c.column_id = fk.key_index_id
), uq as (
	select k.object_id, stuff(( select concat(', [', COLUMN_NAME, ']')
							from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
							where CONSTRAINT_NAME = k.name
							order by COLUMN_NAME asc
							for xml path('')), 1, 2, '') as [definition]
	from sys.sysconstraints as c
	inner join sys.key_constraints as k on k.object_id = c.constid and k.type_desc in ('UNIQUE_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')
), df as (
	select dc.object_id, concat('[', c.name, ']=', dc.definition) as [definition]
	from sys.default_constraints as dc
	inner join sys.columns as c on c.object_id = dc.parent_object_id and c.column_id = dc.parent_column_id
)
select s.name as [schema], t.name as [table], o.name as [constraint], o.type, o.type_desc, i.type_desc as [clustered],
coalesce(cc.definition, fk.definition, uq.definition, df.definition) as [definition]
from sys.sysconstraints as c
inner join sys.objects as o on o.object_id = constid
inner join sys.objects as t on t.object_id = o.parent_object_id
inner join sys.schemas as s on s.schema_id = t.schema_id
left join sys.indexes as i on i.name = o.name
left join sys.check_constraints as cc on cc.object_id = o.object_id
left join fk on fk.object_id = o.object_id
left join uq on uq.object_id = o.object_id
left join df on df.object_id = o.object_id;

Efektem działania tego zapytania będzie w naszym przypadku następujący rezultat.

Teraz, podobnie jak zrobiliśmy to w przypadku tabel, możemy zestawić różnice pomiędzy tymi obiektami.

with fk_prod as (
	select fk.[object_id], concat(s.name, '.', ro.name, '(', c.name, ')') as [definition]
	from poligon.sys.foreign_keys as fk
	inner join poligon.sys.objects as ro on ro.object_id = fk.referenced_object_id
	inner join poligon.sys.schemas as s on s.schema_id = ro.schema_id
	inner join poligon.sys.columns as c on c.object_id = ro.object_id and c.column_id = fk.key_index_id
), fk_dev as (
	select fk.[object_id], concat(s.name, '.', ro.name, '(', c.name, ')') as [definition]
	from poligon_dev.sys.foreign_keys as fk
	inner join poligon_dev.sys.objects as ro on ro.object_id = fk.referenced_object_id
	inner join poligon_dev.sys.schemas as s on s.schema_id = ro.schema_id
	inner join poligon_dev.sys.columns as c on c.object_id = ro.object_id and c.column_id = fk.key_index_id
), uq_prod as (
	select k.object_id, stuff(( select concat(', [', COLUMN_NAME, ']')
							from poligon.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
							where CONSTRAINT_NAME = k.name
							order by COLUMN_NAME asc
							for xml path('')), 1, 2, '') as [definition]
	from poligon.sys.sysconstraints as c
	inner join poligon.sys.key_constraints as k on k.object_id = c.constid and k.type_desc in ('UNIQUE_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')
), uq_dev as (
	select k.object_id, stuff(( select concat(', [', COLUMN_NAME, ']')
							from poligon_dev.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
							where CONSTRAINT_NAME = k.name
							order by COLUMN_NAME asc
							for xml path('')), 1, 2, '') as [definition]
	from poligon_dev.sys.sysconstraints as c
	inner join poligon_dev.sys.key_constraints as k on k.object_id = c.constid and k.type_desc in ('UNIQUE_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')
), df_prod as (
	select dc.object_id, concat('[', c.name, ']=', dc.definition) as [definition]
	from poligon.sys.default_constraints as dc
	inner join poligon.sys.columns as c on c.object_id = dc.parent_object_id and c.column_id = dc.parent_column_id
), df_dev as (
	select dc.object_id, concat('[', c.name, ']=', dc.definition) as [definition]
	from poligon_dev.sys.default_constraints as dc
	inner join poligon_dev.sys.columns as c on c.object_id = dc.parent_object_id and c.column_id = dc.parent_column_id
), baza1 as (
	select s.name as [schema], t.name as [table], o.name as [constraint], o.type, o.type_desc, i.type_desc as [clustered],
	coalesce(cc.definition, fk.definition, uq.definition, df.definition) as [definition]
	from poligon.sys.sysconstraints as c
	inner join poligon.sys.objects as o on o.object_id = constid
	inner join poligon.sys.objects as t on t.object_id = o.parent_object_id
	inner join poligon.sys.schemas as s on s.schema_id = t.schema_id
	left join poligon.sys.indexes as i on i.name = o.name
	left join poligon.sys.check_constraints as cc on cc.object_id = o.object_id
	left join fk_prod as fk on fk.object_id = o.object_id
	left join uq_prod as uq on uq.object_id = o.object_id
	left join df_prod as df on df.object_id = o.object_id
), baza2 as (
	select s.name as [schema], t.name as [table], o.name as [constraint], o.type, o.type_desc, i.type_desc as [clustered],
	coalesce(cc.definition, fk.definition, uq.definition, df.definition) as [definition]
	from poligon_dev.sys.sysconstraints as c
	inner join poligon_dev.sys.objects as o on o.object_id = constid
	inner join poligon_dev.sys.objects as t on t.object_id = o.parent_object_id
	inner join poligon_dev.sys.schemas as s on s.schema_id = t.schema_id
	left join poligon_dev.sys.indexes as i on i.name = o.name
	left join poligon_dev.sys.check_constraints as cc on cc.object_id = o.object_id
	left join fk_dev as fk on fk.object_id = o.object_id
	left join uq_dev as uq on uq.object_id = o.object_id
	left join df_dev as df on df.object_id = o.object_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.[constraint], b2.[constraint]) as [constraint],
b1.[type] as b1_type, b1.[type_desc] as b1_type_desc, b1.[clustered] as b1_clustered, b1.[definition] as b1_definiton, 
b2.[type] as b2_type, b2.[type_desc] as b2_type_desc, b2.[clustered] as b2_clustered, b2.[definition] as b2_definiton
from cmp_baza1 as b1
full outer join cmp_baza2 as b2 on b1.[schema] = b2.[schema] and b1.[table] = b2.[table] and b1.[constraint] = b2.[constraint]

Warto teraz przyjrzeć się wynikowi działania tego zapytania.

Widać tutaj pewien drobny problem. Otóż kiedy jawnie deklarujemy nazwę ograniczenia to wszystko wydaje się działać poprawnie. Kiedy jednak pozostawiamy zadania nadania nazwy samej bazie wtedy nazwa ta wydaje się być raczej losowa. Łatwo to zauważyć porównując ograniczenie domyślnie dotyczące kolumny poziom w tabeli uzytkownicy_uprawnienia. Wiemy, że jest to to samo ograniczenie ale w drugiej bazie nadano mu po prostu inną nazwę. Czy powinniśmy zgłaszać to jako różnicę? Z jeden strony tak – w końcu mamy dwa obiekty o różnych nazwach. Z drugiej jednak chcielibyśmy na pewno wiedzieć, że merytoryczny aspekt ograniczenia różnic nie wykazuje.

Oczywiście warto od razu wspomnieć jedną z dobrych praktyk stosowanych przez wielu bazodanowców, która głosi, że staramy się nie tworzyć nienazwanych ograniczeń. Dobrze jest wyrobić w sobie odruch nadawania tych nazw. W prawdziwych bazach produkcyjnych, kiedy trzeba wprowadzać jakieś zmiany takie nazwane przez bazę obiekty potrafią całkiem dobrze podnieść poziom irytacji. Zresztą daleko szukać nie trzeba – właśnie sami mogliśmy zobaczyć dobry przykład takiego beztroskiego podejścia 😉

Czy możemy jakoś rozpoznać takie domyślnie utworzone nazwy? Kiedy na nie patrzymy to można powiedzieć, że tak – wyglądają nieco „dziwnie”. Problem jednak w tym, że zdefiniowanie precyzyjnego zapytania do rozpoznawania „dziwnych” nazw jest zadaniem trudnym o ile w ogóle możliwym. Najlepiej będzie moim zdaniem założyć, że takie rozpoznawanie nie ma sensu.

Rozwiązaniem problemu jakie chciałbym zaproponować jest dodanie dodatkowego pola (nazwałem je potential_second_name) z informacją o potencjalnej innej nazwie tożsamego ograniczenia w drugiej bazie (ma to sens tylko wtedy jeśli w danym wierszu różnicy ograniczenie istnieje tylko w jednej z baz). Naszym kryterium wyszukiwania będzie zgodność całej definicji ograniczenia za wyjątkiem samej jego nazwy. Aby to uzyskać musimy rozbudować nasze zapytanie w sposób jak poniżej.

with fk_prod as (
	select fk.[object_id], concat(s.name, '.', ro.name, '(', c.name, ')') as [definition]
	from poligon.sys.foreign_keys as fk
	inner join poligon.sys.objects as ro on ro.object_id = fk.referenced_object_id
	inner join poligon.sys.schemas as s on s.schema_id = ro.schema_id
	inner join poligon.sys.columns as c on c.object_id = ro.object_id and c.column_id = fk.key_index_id
), fk_dev as (
	select fk.[object_id], concat(s.name, '.', ro.name, '(', c.name, ')') as [definition]
	from poligon_dev.sys.foreign_keys as fk
	inner join poligon_dev.sys.objects as ro on ro.object_id = fk.referenced_object_id
	inner join poligon_dev.sys.schemas as s on s.schema_id = ro.schema_id
	inner join poligon_dev.sys.columns as c on c.object_id = ro.object_id and c.column_id = fk.key_index_id
), uq_prod as (
	select k.object_id, stuff(( select concat(', [', COLUMN_NAME, ']')
							from poligon.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
							where CONSTRAINT_NAME = k.name
							order by COLUMN_NAME asc
							for xml path('')), 1, 2, '') as [definition]
	from poligon.sys.sysconstraints as c
	inner join poligon.sys.key_constraints as k on k.object_id = c.constid and k.type_desc in ('UNIQUE_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')
), uq_dev as (
	select k.object_id, stuff(( select concat(', [', COLUMN_NAME, ']')
							from poligon_dev.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
							where CONSTRAINT_NAME = k.name
							order by COLUMN_NAME asc
							for xml path('')), 1, 2, '') as [definition]
	from poligon_dev.sys.sysconstraints as c
	inner join poligon_dev.sys.key_constraints as k on k.object_id = c.constid and k.type_desc in ('UNIQUE_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')
), df_prod as (
	select dc.object_id, concat('[', c.name, ']=', dc.definition) as [definition]
	from poligon.sys.default_constraints as dc
	inner join poligon.sys.columns as c on c.object_id = dc.parent_object_id and c.column_id = dc.parent_column_id
), df_dev as (
	select dc.object_id, concat('[', c.name, ']=', dc.definition) as [definition]
	from poligon_dev.sys.default_constraints as dc
	inner join poligon_dev.sys.columns as c on c.object_id = dc.parent_object_id and c.column_id = dc.parent_column_id
), baza1 as (
	select s.name as [schema], t.name as [table], o.name as [constraint], o.type, o.type_desc, i.type_desc as [clustered],
	coalesce(cc.definition, fk.definition, uq.definition, df.definition) as [definition]
	from poligon.sys.sysconstraints as c
	inner join poligon.sys.objects as o on o.object_id = constid
	inner join poligon.sys.objects as t on t.object_id = o.parent_object_id
	inner join poligon.sys.schemas as s on s.schema_id = t.schema_id
	left join poligon.sys.indexes as i on i.name = o.name
	left join poligon.sys.check_constraints as cc on cc.object_id = o.object_id
	left join fk_prod as fk on fk.object_id = o.object_id
	left join uq_prod as uq on uq.object_id = o.object_id
	left join df_prod as df on df.object_id = o.object_id
), baza2 as (
	select s.name as [schema], t.name as [table], o.name as [constraint], o.type, o.type_desc, i.type_desc as [clustered],
	coalesce(cc.definition, fk.definition, uq.definition, df.definition) as [definition]
	from poligon_dev.sys.sysconstraints as c
	inner join poligon_dev.sys.objects as o on o.object_id = constid
	inner join poligon_dev.sys.objects as t on t.object_id = o.parent_object_id
	inner join poligon_dev.sys.schemas as s on s.schema_id = t.schema_id
	left join poligon_dev.sys.indexes as i on i.name = o.name
	left join poligon_dev.sys.check_constraints as cc on cc.object_id = o.object_id
	left join fk_dev as fk on fk.object_id = o.object_id
	left join uq_dev as uq on uq.object_id = o.object_id
	left join df_dev as df on df.object_id = o.object_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.[constraint], b2.[constraint]) as [constraint],
b1.[type] as b1_type, b1.[type_desc] as b1_type_desc, b1.[clustered] as b1_clustered, b1.[definition] as b1_definiton, 
b2.[type] as b2_type, b2.[type_desc] as b2_type_desc, b2.[clustered] as b2_clustered, b2.[definition] as b2_definiton,
case
	when b1.[type] is not null and b2.[type] is null then (	select [constraint]
															from cmp_baza2 
															where b1.[schema] = [schema] 
															and b1.[table] = [table] 
															and b1.[type] = [type]
															and b1.[type_desc] = [type_desc]
															and coalesce(b1.[clustered], '') = coalesce([clustered], '')
															and coalesce(b1.definition, '') = coalesce([definition], ''))
	when b2.[type] is not null and b1.[type] is null then (	select [constraint]
															from cmp_baza1 
															where b2.[schema] = [schema] 
															and b2.[table] = [table] 
															and b2.[type] = [type]
															and b2.[type_desc] = [type_desc]
															and coalesce(b2.[clustered], '') = coalesce([clustered], '')
															and coalesce(b2.definition, '') = coalesce([definition], ''))
	else null
end as potential_second_name
from cmp_baza1 as b1
full outer join cmp_baza2 as b2 on b1.[schema] = b2.[schema] and b1.[table] = b2.[table] and b1.[constraint] = b2.[constraint]

Warto zwrócić uwagę, że w polach, w których można spodziewać się wartości NULL warto użyć czegokolwiek co pozwoli uniknąć porównywania wartości z NULL (takie porównanie nie zadziała). Ja użyłem najzwyklejszego coalesce. Efektem powyższego zapytania będzie następujący, dosyć szeroki ze względu na liczbę kolumn, widok.

Jak widać skrypt całkiem nieźle poradził sobie ze znalezieniem potencjalnej nazwy tego samego ograniczenia w drugiej bazie. Wszystkie trzy analogiczne ograniczenia w każdej z baz zostały poprawnie zidentyfikowane w drugiej.

Na koniec, tradycyjnie już, możemy opakować sobie powyższe zapytanie w wygodną procedurę, którą można umieścić w bazie master. Procedurę taką nazywałem dbo.migracje_sprawdzOgraniczenia. Podobnie jak w poprzednich częściach parametryzuje się ją 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 równie proste jak poprzednie – 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_sprawdzOgraniczenia 'poligon', 'poligon_dev';

Ponieważ dzisiejszy temat był dosyć złożony dlatego w kolejnej części cyklu postaram się pokazać coś łatwiejszego – zajmiemy się wykrywaniem różnic w rolach użytkowników. Od razu zapraszam do lektury wpisu 🙂