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
chcę dodać kolumnę, która będzie połączeniem dwóch innych istniejących już kolumn, jak powinien wyglądać scrypt?
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!