Spis treści:

Kategoria:SQL Server


Jak pobrać informacje o wszystkich kluczach obcych w SQL Server

Powody mogą być różne, ale gdyby ktoś potrzebował pobrać informacje o wszystkich kluczach obcych w bazie lub dla poszczególnej tabeli to istnieje kilka możliwości. Oprócz zwykłego przeklikania w SQL Server Management Studio istnieją sposoby skryptowe. Poniższy skrypt pozwala wyświetlić nazwy wszystkich kluczy określając jednocześnie nazwę tabeli i kolumny pełniącej funkcję klucza obcego, a także nazwę tabeli i kolumny pełniącej w tej relacji funkcję klucza głównego. Oto pierwsza metoda korzystająca z widoków INFORMATION_SCHEMA.

SELECT C.CONSTRAINT_NAME [Constraint_Name],
FT.TABLE_SCHEMA+'.'+FT.TABLE_NAME [Table], FCU.COLUMN_NAME [Column],
PT.TABLE_SCHEMA+'.'+PT.TABLE_NAME RefTable, PCU.COLUMN_NAME RefColumn
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FT
    ON C.CONSTRAINT_NAME = FT.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PT
    ON C.UNIQUE_CONSTRAINT_NAME = PT.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FCU
    ON C.CONSTRAINT_NAME = FCU.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PCU
    ON PT.CONSTRAINT_NAME=PCU.CONSTRAINT_NAME

Można oczywiście dołożyć warunek WHERE i wstawić tam co nam się żywnie podoba. Dość częstą praktyką jest przefiltrowanie wyników po nazwie tabeli. Możemy w ten sposób otrzymać wszystkie klucze obce wskazujące na daną tabelę (czyli tzw. zależności między tabelami lub jak to nazwano z angielskiego w Management Studio - dependencies).

Istnieje jeszcze inna metoda. Plan wykonania i testy sugerują, że jest wyraźnie szybsza od pierwszej metody. Metoda druga także korzysta z widoków systemowych, ale innych. Tym razem trzon zapytania to widoki sys.foreign_keys oraz sys.foreign_key_columns.

SELECT FK.name, PS.name+'.'+PT.name, PC.name, RS.name+'.'+RT.name, RC.name
FROM sys.foreign_keys FK
JOIN sys.foreign_key_columns FKC ON FK.[object_id]=FKC.[constraint_object_id]
JOIN sys.columns PC ON FKC.parent_object_id = PC.[object_id]
    AND FKC.parent_column_id=PC.column_id
JOIN sys.tables PT ON PC.[object_id]=PT.[object_id]
JOIN sys.schemas PS ON PS.[schema_id]=PT.[schema_id]
JOIN sys.columns RC ON FKC.referenced_object_id = RC.[object_id]
    AND FKC.referenced_column_id=RC.column_id
JOIN sys.tables RT ON RC.[object_id]=RT.[object_id]
JOIN sys.schemas RS ON RS.[schema_id]=RT.[schema_id]

Zapytanie można uprościć, jeżeli nie potrzebujemy schematu. Do pobrania nazwy tabeli można wykorzystać funkcję OBJECT_NAME - wówczas nasze zapytanie skróci się jeszcze bardziej.

Kategoria:SQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
To samo pytanie co wyżej. Mam za zadanie dodać kolumnę do istniejącej tabeli łącząc obie inne kolumny ze sobą, ale nie mam pojęcia jak za to się zabrać
działa :) tylko była literówka :)
Podziękował. Trochę późno, po 8 latach, ale dzięki za testy (rozumiem że dla SQL2012 robione). Tak się właśnie zastanawiałem ile złego czynię stosując czasem __(max).
Super robota, korzystając z innych internetowych kalkulatorow po prostu wątpiłem w ich prawdomówność, w końcu trafiłem tutaj i wynik w końcu jest wiarygodny. 40 km w 2 h 810 kcal, ciekawostka: na fitatu wyliczyło mi 5700 kcal 😊 najlepiej będzie chyba jak kupię zegarek sportowy.
Wielkie dzieki za solidne wyjasnienia tematu.