ALTER i DROP w SQL Server: znajdź zależności tabeli, zanim to uruchomisz

Jak znaleźć zależności tabeli przed DROP lub ALTER kolumny w SQL Server — z użyciem sys.dm_sql_referencing_entities, sys.foreign_keys i widoków zależności.

Autor: Łukasz Żochowski

  • #sql
  • #sqlserver
  • #ssms
  • #database

DROP TABLE albo ALTER TABLE ... ALTER COLUMN to jednolinijkowe polecenie o zasięgu rażenia, którego z samego polecenia nie widać. Tabela, którą właśnie zmieniasz, może być czytana przez kilkanaście widoków, trzy procedury raportowe, trigger i klucz obcy z tabeli potomnej, o której zdążyłeś zapomnieć. SQL Server zwykle i tak pozwoli Ci wykonać zmianę — a awaria wychodzi później, na produkcji, jako widok, który już się nie kompiluje, albo zapytanie aplikacji, które nagle zwraca błąd.

Bezpieczny nawyk jest prosty: przeczytaj zależności, zanim uruchomisz polecenie. SQL Server większość z nich już dla Ciebie śledzi — w widokach katalogowych i dynamicznych funkcjach zarządzania. Ten poradnik pokazuje dokładne zapytania do uruchomienia najpierw, co każde z nich wyłapuje i — równie ważne — czego nie wyłapie żadne z nich.

Ujawnienie: ten blog publikuje LumaSoft (LUMA sp. z o.o.), a ja pracuję nad narzędziem wspomnianym pod koniec.

Co właściwie się psuje przy ALTER lub DROP?

Dwa różne polecenia, dwa różne rodzaje szkód.

DROP usuwa obiekt, na który inne rzeczy mogą wskazywać. Jeśli widok, procedura lub funkcja odwołuje się po nazwie do usuwanej tabeli, nie zawiedzie w momencie usunięcia — zawiedzie, gdy ktoś następnym razem to uruchomi. Jeśli tabela potomna ma klucz obcy wskazujący na tabelę, którą usuwasz, silnik wprost odmówi usunięcia, dopóki nie zniknie to ograniczenie.

ALTER COLUMN jest subtelniejszy. Zmiana typu, długości, dopuszczalności NULL czy sortowania kolumny może zepsuć wszystko, co jest z tą kolumną związane: widoki i funkcje związane schematem (utworzone WITH SCHEMABINDING), kolumny obliczane, ograniczenia CHECK i DEFAULT, indeksy obejmujące kolumnę oraz każdą procedurę lub funkcję zakładającą dawny kształt. Część z nich zablokuje ALTER; część przepuści go i zacznie się mylić dopiero potem.

Są więc trzy pytania do odpowiedzenia, zanim czegokolwiek dotkniesz: które obiekty odwołują się do tego po nazwie, które klucze obce na niego wskazują oraz — dla ALTER COLUMN — kto faktycznie używa konkretnej kolumny, którą zmieniam. SQL Server odpowiada na wszystkie trzy.

Jak znaleźć, co odwołuje się do tabeli w SQL Server?

Zacznij od pytania na poziomie obiektu: które widoki, procedury, funkcje i triggery wymieniają tę tabelę po nazwie? Dynamiczna funkcja zarządzania sys.dm_sql_referencing_entities zwraca dokładnie to — po jednym wierszu na encję w bieżącej bazie, która odwołuje się do wskazanego obiektu (Microsoft Learn: sys.dm_sql_referencing_entities):

-- Co zależy od tej tabeli? (widoki, procedury, funkcje, triggery)
SELECT
    referencing_schema_name,
    referencing_entity_name,
    referencing_class_desc,
    is_caller_dependent
FROM sys.dm_sql_referencing_entities('Sales.Customer', 'OBJECT');

Drugi argument to klasa obiektu referowanego — OBJECT, TYPE, XML_SCHEMA_COLLECTION albo PARTITION_FUNCTION; dla tabel i widoków użyj OBJECT. Każdy wiersz to coś, co by się zepsuło — lub wymagało przejrzenia — gdybyś usunął Sales.Customer. Pusty wynik oznacza, że nic w tej bazie nie odwołuje się do niego po nazwie — zwróć uwagę na zaznaczenie, bo dokumentacja jasno mówi, że funkcja „nie zwraca encji w innych bazach danych”.

Jak znaleźć klucze obce wskazujące na tabelę?

Klucze obce to osobny rodzaj zależności: nie są wyrażeniami SQL, więc funkcja powyżej ich nie raportuje. Mieszkają we własnym widoku katalogowym sys.foreign_keys, który przechowuje po jednym wierszu na ograniczenie klucza obcego (Microsoft Learn: sys.foreign_keys). Żeby znaleźć klucze przychodzące — tabele potomne, których ograniczenia odwołują się do tabeli przeznaczonej do usunięcia — filtruj po referenced_object_id:

-- Które klucze obce wskazują NA tę tabelę? (tabele potomne blokujące DROP)
SELECT
    fk.name                                  AS foreign_key_name,
    OBJECT_SCHEMA_NAME(fk.parent_object_id)  AS child_schema,
    OBJECT_NAME(fk.parent_object_id)         AS child_table,
    fk.delete_referential_action_desc,
    fk.is_disabled
FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = OBJECT_ID('Sales.Customer');

Tutaj parent_object_id to tabela, która jest właścicielem ograniczenia (potomna), a referenced_object_id to tabela referowana (Twój cel). Kolumna delete_referential_action_desc mówi, czy usunięcie kaskaduje, ustawia NULL, czy nie robi nic — a to ma ogromne znaczenie, jeśli planowałeś wyczyścić wiersze przed usunięciem. Każdy wiersz tutaj to ograniczenie, które musisz najpierw usunąć lub przekierować.

Jak sprawdzić zależności na poziomie kolumny przed ALTER COLUMN?

ALTER COLUMN potrzebuje precyzyjniejszej odpowiedzi niż „co odwołuje się do tabeli”. Chcesz wiedzieć, kto dotyka tej kolumny. Pomagają dwa widoki.

Po pierwsze, sys.sql_expression_dependencies daje każdą zależność po nazwie względem encji i — co kluczowe — oznacza odwołania związane schematem, czyli te, które twardo zablokują Twój ALTER (Microsoft Learn: sys.sql_expression_dependencies). Zapytanie odwrotne (co odwołuje się do mojego obiektu) wygląda tak:

-- Wszystko, co odwołuje się do Sales.Customer po nazwie, z flagą schema-bound
SELECT
    OBJECT_SCHEMA_NAME(sed.referencing_id) AS referencing_schema,
    OBJECT_NAME(sed.referencing_id)        AS referencing_name,
    o.type_desc,
    sed.is_schema_bound_reference
FROM sys.sql_expression_dependencies AS sed
JOIN sys.objects AS o
    ON sed.referencing_id = o.object_id
WHERE sed.referenced_id = OBJECT_ID(N'Sales.Customer');

Wiersze z is_schema_bound_reference = 1 są najpilniejsze: widok lub funkcja związana schematem przytwierdza kolumnę w miejscu, a silnik odrzuci ALTER, dopóki tego nie zmienisz.

Po drugie, aby potwierdzić, czy konkretny moduł czyta lub zapisuje kolumnę, skieruj na ten moduł sys.dm_sql_referenced_entities. Zwraca szczegół na poziomie kolumny, w tym to, czy każda kolumna jest wybierana (is_selected) czy aktualizowana (is_updated) (Microsoft Learn: sys.dm_sql_referenced_entities):

-- Czy ta procedura czyta lub zapisuje kolumnę, którą zaraz zmienię?
SELECT
    referenced_entity_name AS table_name,
    referenced_minor_name  AS column_name,
    is_selected,
    is_updated,
    is_select_all
FROM sys.dm_sql_referenced_entities('Sales.uspUpdateCustomer', 'OBJECT');

Kolumna oznaczona is_updated = 1, której typ właśnie zamierzasz zwęzić, to dokładnie to, co chcesz wyłapać przed zmianą, a nie po tym, jak padnie kolejne nocne zadanie.

Czego te zapytania nie wyłapią?

To część warta przyswojenia, bo sprawdzenie zależności, które daje fałszywą pewność, jest gorsze niż żadne.

  • Dynamiczny SQL i kod aplikacji. Te widoki śledzą utrwalone odwołania po nazwie wewnątrz modułów SQL. Zapytanie złożone jako tekst przez EXEC(@sql) albo zbudowane w warstwie C# czy Pythona jest dla nich niewidzialne. Takie zależności żyją tylko w kodzie, który musisz przeszukać sam.
  • Odwołania międzybazowe. sys.dm_sql_referencing_entities raportuje tylko bieżącą bazę. Widok w innej bazie, który czyta z Twojej tabeli, się nie pojawi. sys.sql_expression_dependencies zapisuje nazwy międzybazowe, ale nie potrafi rozwiązać ich identyfikatorów.
  • Stare sp_depends. Jeśli uczyłeś się tego na starszym serwerze, porzuć ten nawyk: Microsoft oznacza sp_depends jako wycofywane — „Ta funkcja zostanie usunięta w przyszłej wersji SQL Server… Zamiast niej użyj sys.dm_sql_referencing_entities i sys.dm_sql_referenced_entities” (Microsoft Learn: sp_depends).

Traktuj widoki katalogowe jako mocny pierwszy przebieg, a potem potwierdź przeszukaniem repozytoriów aplikacji pod kątem wszystkiego, co budowane dynamicznie.

Jaki jest odpowiednik w PostgreSQL i MySQL?

Krótko, bo zasada się przenosi. PostgreSQL zapisuje zależności w katalogu pg_depend i opiera się na nich przy usuwaniu: DROP domyślnie działa jako RESTRICT, który „odmawia usunięcia tabeli, jeśli zależą od niej jakiekolwiek obiekty”, podczas gdy CASCADE usuwa także zależne (PostgreSQL: pg_depend, DROP TABLE). Silnik chroniący Cię domyślnie to realna przewaga — ale i tak chcesz wiedzieć, co CASCADE zabierze ze sobą, zanim to wpiszesz. MySQL udostępnia relacje kluczy obcych przez information_schema.KEY_COLUMN_USAGE; filtruj po REFERENCED_TABLE_NAME, by znaleźć ograniczenia wskazujące na tabelę (MySQL: KEY_COLUMN_USAGE).

Wniosek

Każde zapytanie powyżej to SELECT na widoku systemowym. Są tylko do odczytu, natychmiastowe i darmowe — nie ma powodu, żeby nie uruchomić ich przed zmianą strukturalną. Przeczytanie zależności najpierw zamienia ALTER i DROP z hazardu w decyzję, której da się bronić.

To także mały przykład większej zasady. Wiedza o tym, co zależy od tabeli, to fakt, który sprawdzasz w żywej bazie, a nie coś, co wywnioskujesz z pamięci albo nieaktualnego diagramu — i jest to dokładnie to, co asystent AI powinien odczytać z Twojego prawdziwego schematu, zamiast zgadywać. Ponieważ te zapytania o zależności są czystymi odczytami, mieszczą się też wygodnie w przepływie tryb tylko do odczytu domyślnie: patrz, zanim skoczysz, i trzymaj patrzenie osobno od skakania.

Jeśli pracujesz w SQL Server Management Studio, SQL Sage dla SSMS to dodatek, który umieszcza asystenta AI w edytorze zapytań — pisze, objaśnia i optymalizuje SQL ugruntowany w Twoim prawdziwym schemacie, pozostaje domyślnie w trybie tylko do odczytu (SELECT się wykonuje; wszystko, co zmienia dane lub strukturę, czeka na Twoje wyraźne potwierdzenie) i wysyła do modelu wyłącznie Twój schemat i tekst SQL, nigdy wierszy danych, chyba że wyrazisz na to zgodę w danej sesji. Jest bezkluczowy — logujesz się własnym kontem Claude lub ChatGPT, bez klucza API do zarządzania. Jest 30-dniowy trial bez karty, potem darmowy plan. Zobaczysz go i bliźniaczą wtyczkę do DBeavera na stronie produktów SQL Sage. SQL Sage tworzy LumaSoft (LUMA sp. z o.o.) i nie jest związany z Microsoftem.

← Wróć do bloga