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.
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.
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
Brak komentarzy - bądź pierwszy