Tabele systemowe – sojusznik programisty bazy danych



Jako programista baz danych z pewnością napotykasz liczne wyzwania. Być może są to kłopoty związane z pochłanianiem zasobów sieciowych lub zarządzaniem danymi i ich agregowaniem. Czy wiesz, że wiele z problemów natury bazodanowej możesz w łatwy sposób rozwiązać za pomocą tabel systemowych, jakie dostarczane są w pakiecie wraz z serwerem bazodanowym SQL Server?


Tabele te pomagają w sposób automatyczny rozwiązać problem występujący na poziomie tabeli bazy danych, ekstrapolując go do wszystkich wystąpień tego problemu w obszarze całej bazy danych. Jest to możliwe dzięki przewidywaniu potencjalnych problemów na podstawie wiedzy o istniejącym pojedynczym problemie w bazie danych. Tabele systemowe ułatwiają również wyszukiwanie pożądanych obiektów, bez skrupulatnego i czasochłonnego przeglądania bazy element po elemencie.



Usuwanie i odtwarzanie kluczy obcych

Jednym z problemów z jakimi się osobiście spotkałem, związany jest ściśle z kluczami obcymi stworzonymi na tabelach. Kłopotem mogą być wymiary hurtowni danych. Przykładowo, częstą sytuacją jest konieczność przesilenia całej tabeli od początku lub przeładowanie części danych. O zgrozo, nie dotyczy to jedynie baz testowych. Ten problem nierzadko zdarza się również w bazach produkcyjnych. Jak powszechnie wiadomo, nie można ani usunąć całej tabeli, na bazie której choć jeden atrybut jest kluczem obcym w innej tabeli, ani nawet kilku rekordów, jeśli do nich odwołują się jakiekolwiek rekordy z innych tabel. Możemy oczywiście ręcznie próbować wyszukać wszystkie klucze, których usunięcie spowoduje możliwość usunięcia naszej tabeli lub rekordów w niej występujących...

W jaki sposób uniknąć tego mozolnego procesu?

Z pomocą przychodzą tutaj tabele systemowe. Otóż, wykorzystując jedno zapytanie, jesteśmy w stanie wygenerować polecenia, dzięki którym w łatwy i nieinwazyjny sposób całkowicie usuniemy odpowiednie klucze. W efekcie, możemy wykonać naprawę danych, a następnie odtworzyć usunięte klucze. Wszystko dzięki tabelom systemowym.

Przykład operacji na bazie AdventureWorks2017

Aby przybliżyć działanie tabeli systemowych, przytoczę konkretny przykład. Oto moje doświadczenie z usunięciem tabeli dbo.DimCurrency.

Pierwsza próba jej usunięcia kończy się spodziewanym błędem:

Nie możemy usunąć tabeli, ponieważ są do niej odniesienia w postaci kluczy obcych. Jak już wspomniałem wyżej, możemy ręcznie przeszukać tabele na bazie danych i sprawdzić gdzie ukrywają się klucze obce wskazujące na naszą tabelę dbo.DimCurrency. Brzmi to jednak co najmniej nieprofesjonalnie. Mówi się też, że programista z reguły jest osobą leniwą, więc zdecydowanie w tym wypadku będzie wolał skorzystać z tabel systemowych. Konkretnie z dwóch: sys.foreign_key_columns i information_schema.columns:


SELECT OBJECT_SCHEMA_NAME(parent_object_id) AS SchematTabeliZawierającejKluczObcy
 ,OBJECT_NAME(parent_object_id) AS NazwaTabeliZawierającejKluczObcy
 ,OBJECT_SCHEMA_NAME(referenced_object_id) AS SchematTabeliDoKtórejKluczObcySięOdnosi
 ,OBJECT_NAME(referenced_object_id) AS NazwaTabeliDoKtórejKluczObcySięOdnosi
 ,OBJECT_NAME(constraint_object_id) AS NazwaKluczaObcego
 ,isc2.Column_Name AS NazwaKolumnyNaKtórejJestKluczObcy
,'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + OBJECT_NAME(constraint_object_id) AS UsuwanieKluczy
,'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] WITH CHECK ADD CONSTRAINT ' + OBJECT_NAME(constraint_object_id) + ' FOREIGN KEY(' + isc2.Column_Name + ') 
  REFERENCES ' + OBJECT_SCHEMA_NAME(referenced_object_id) + '.' + OBJECT_NAME(referenced_object_id) + ' ('  + isc.Column_Name + ')' AS DodawanieKluczy
FROM sys.foreign_key_columns FKC
INNER JOIN information_schema.columns ISC ON OBJECT_SCHEMA_NAME(referenced_object_id) = isc.Table_schema
 AND OBJECT_NAME(referenced_object_id) = isc.Table_name
 AND referenced_column_id = isc.Ordinal_Position
INNER JOIN information_schema.columns ISC2 ON OBJECT_SCHEMA_NAME(parent_object_id) = isc2.Table_schema
 AND OBJECT_NAME(parent_object_id) = isc2.Table_name
 AND parent_column_id = isc2.Ordinal_Position
WHERE referenced_object_id = object_id('[dbo].[DimCurrency]')

Przy odpowiednim wykorzystaniu dostępnych kolumn możemy wygenerować zapytania usuwające wszystkie klucze do tabeli dbo.DimCurrency – kolumna UsuwanieKluczy – oraz odtwarzające wszystkie wcześniej istniejące klucze – kolumna DodawanieKluczy. W ten sposób nie marnujemy cennego czasu na ręczne poszukiwanie kluczy obcych do tabeli. Co więcej, jesteśmy w 100% pewni, że o żadnym z nich nie zapomnieliśmy.

Perspektywa programisty baz danych - kiedyś i teraz

Możliwości tabel systemowych są oczywiście dużo większe niż tylko te przedstawione tutaj. Potencjał, jaki w nich drzemie jest nie do ocenienia i tylko od kreatywności programisty zależy w jakim stopniu będą one w stanie mu pomóc w pracy. Używając zapytań musimy oczywiście uważać i oceniać skutki naszych działań. Nierozsądne korzystanie z tabel i chęć ich całkowitego zautomatyzowania może bowiem spowodować spore problemy, włącznie z utratą cennych danych.


Sam przez pewien czas kompletnie lekceważyłem informacje, które można znaleźć w tabelach systemowych. Ograniczałem się tylko do pojedynczych zapytań wyciągających np. listę tabel i ich kolumn. W obecnym momencie kariery nie wyobrażam sobie działań na projektach bez wsparcia mojej pracy ze strony zestawu tabel systemowych. Zapytanie wskazane przeze mnie powyżej już niejednokrotnie oszczędziło mi czasu i nerwów podczas żmudnego procesu naprawiania danych. Nie da się jednak ukryć, że takie zapytanie usprawnia głównie prace w obszarach testowych baz danych. A czy to właśnie nie tam potrzebujemy najwięcej czasu i każda możliwość jego odzyskania jest na wagę złota?


Karol Jurek

Senior BI Developer w BitPeak

karol.jurek@bitpeak.pl



Jeśli chcecie dołączyć do naszego zespołu, zachęcamy do zostawienia swojego CV na adres mailowy: office@bitpeak.pl