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
Jeśli chcecie dołączyć do naszego zespołu, zachęcamy do zostawienia swojego CV na adres mailowy: office@bitpeak.pl