Znajdź klucze obce powiązane z danym kluczem podstawowym

Chcę sposób, aby ustalić, które kolumny w danej bazie danych są połączone za pomocą relacji PK / FK. Mogę zwrócić informacje PK / FK dla danej tabeli za pośrednictwem

SELECT *  
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu 
WHERE EXISTS (
    SELECT tc.* 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
    WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'  
        AND tc.TABLE_NAME = 'MyTable'  
        /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO

ale w przypadku PK zwróconego z takiego zapytania, w jaki sposób ustanowić powiązany FK (zakładając, że taki istnieje)?

Wiem, że możesz również uzyskać tabele, do których istnieją odniesienia, za pośrednictwem:

SELECT CONSTRAINT_NAME = name, 
       FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id), 
       FOREIGN_TABLE = OBJECT_NAME(parent_object_id), 
       REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id), 
       REFERENCED_TABLE = OBJECT_NAME(referenced_object_id) 
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO

ale walczę teraz, aby uzyskać wyraźne odwołania do kolumn.

Tworzę generator skryptów dla QlikView. Aby wygenerować skrypt, potrzebuję ograniczeń i powiązanych linków. Potrzebuję wszystkich informacji o ograniczeniach dla dowolnej kolumny (jeśli występują).

Chcę zbudować klasę bazy danych, która przechowuje wszystkie informacje dla danej bazy danych. Ta struktura klas database.table.column.constraintszostanie następnie wykorzystana do uzyskania zgodności między różnymi kolumnami na PK / FK.

Oczywiście niektóre kolumny będą miały tylko FK, w tym przypadku chcę również pobrać informacje o PK odpowiedniego klucza; niektóre będą miały tylko PK, a potem chcę odwrócić. Niektóre oczywiście mogą mieć jedno i drugie.

Oto proste zapytanie, aby dopasować klucze obce do odwoływanych tabel / kolumn:

SELECT
    o1.name AS FK_table,
    c1.name AS FK_column,
    fk.name AS FK_name,
    o2.name AS PK_table,
    c2.name AS PK_column,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c1
        ON fkc.parent_object_id = c1.object_id
        AND fkc.parent_column_id = c1.column_id
    INNER JOIN sys.columns c2
        ON fkc.referenced_object_id = c2.object_id
        AND fkc.referenced_column_id = c2.column_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

Dane wyjściowe mają osiem kolumn: nazwy tabeli i kolumny dla kluczy obcych (FK_table, FK_column), nazwy ograniczeń klucza obcego (FK_name), przywoływane PK lub unikalne nazwy indeksu tabeli i kolumn (PK_table, PK_column), nazwa przywoływanego PK lub indeksu unikalnego (PK_name) oraz akcje kaskadowe aktualizacji / usuwania (Delete_Action, Update_Action).

(Edytowane, aby dodać więcej kolumn wyjściowych).

EDYCJA: Wróciłem 6 lat później z ulepszoną wersją tego. Uświadomiłem sobie, że oryginalne zapytanie tak naprawdę nie obsługuje dobrze wielu kolumn obcych kluczy, i chciałem również móc szybko zidentyfikować wyłączone, niezaufane lub nieindeksowane klucze obce. Oto nowa wersja, która to wszystko koryguje.

Klucze wielokolumnowe są wyświetlane jako listy oddzielone przecinkami w FK_columnsi PK_columnsprzy użyciu tradycyjnego FOR XML/ STUFFnadużycia. W FK_indexeskolumna pokazuje nazwy wszystkich indeksów w tabeli klucza obcego, które mogą być potencjalnie wykorzystane do zaspokojenia zmierza za pomocą kolumn klucza obcego (głównie na optymalizacji usuwa lub aktualizacje do tabeli klucza podstawowego). Jeśli tak NULL, to masz nieindeksowany klucz obcy. Możesz dostosować ORDER BYlub dodać WHEREklauzulę (skomentowane poniżej), jeśli chcesz sortować według nazwy tabeli PK, filtrować określone tabele PK / FK itp.

SELECT
    fk.is_disabled,
    fk.is_not_trusted,
    OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
    o1.name AS FK_table,
    --Generate list of columns in referring side of foreign key
    STUFF(
        (
            SELECT ', ' + c1.name AS [text()]
            FROM sys.columns c1 INNER
                JOIN sys.foreign_key_columns fkc
                    ON c1.object_id = fkc.parent_object_id
                    AND c1.column_id = fkc.parent_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS FK_columns,
    --Look for any indexes that will fully satisfy the foreign key columns
    STUFF(
        (
            SELECT ', ' + i.name AS [text()]
            FROM sys.indexes i
            WHERE i.object_id = o1.object_id
                AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
                    SELECT fkc.constraint_column_id, fkc.parent_column_id
                    FROM sys.foreign_key_columns fkc
                    WHERE fkc.constraint_object_id = fk.object_id
                    EXCEPT
                    SELECT ic.key_ordinal, ic.column_id
                    FROM sys.index_columns ic
                    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                )
            FOR XML PATH('')
        ), 1, 2, '') AS FK_indexes,
    fk.name AS FK_name,
    OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
    o2.name AS PK_table,
    --Generate list of columns in referenced (i.e. PK) side of foreign key
    STUFF(
        (
            SELECT ', ' + c2.name AS [text()]
            FROM sys.columns c2
                INNER JOIN sys.foreign_key_columns fkc
                    ON c2.object_id = fkc.referenced_object_id
                    AND c2.column_id = fkc.referenced_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS PK_columns,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name

To zapytanie zawiera wszystkie relacje FK w bazie danych - nazwa ograniczenia FK, schemat / tabela tabeli odniesienia, nazwa kolumny odniesienia, schemat / tabela tabeli odniesienia oraz nazwa kolumny odniesienia. Będzie wiele wierszy dla ograniczenia wielokolumnowego.

SELECT 
    FK = OBJECT_NAME(pt.constraint_object_id),
    Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
            + '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
    Referencing_col = QUOTENAME(pc.name), 
    Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.referenced_object_id)) 
            + '.' + QUOTENAME(OBJECT_NAME(pt.referenced_object_id)),
    Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
ORDER BY Referencing_table, FK, pt.constraint_column_id;

Jeśli szukasz kolumn z określonego ograniczenia klucza podstawowego i znasz już nazwę tego ograniczenia PK, możesz napisać to:

DECLARE @PK_Constraint SYSNAME = N'Name of PK constraint';

SELECT
    FK = OBJECT_NAME(fkc.constraint_object_id),
    Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
            + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
    Referencing_col = QUOTENAME(pc.name), 
    Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id)) 
            + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
    Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
WHERE EXISTS 
(
  SELECT 1 FROM sys.indexes AS i
  INNER JOIN sys.foreign_keys AS fk
  ON i.[object_id] = fk.referenced_object_id
  AND i.index_id = fk.key_index_id
  AND fk.[object_id] = fkc.constraint_object_id
  AND i.name = @PK_Constraint
)
ORDER BY Referencing_table, FK, fkc.constraint_column_id;

Jeśli chcesz dołączyć nazwę PK wraz z innymi informacjami:

SELECT 
    FK = OBJECT_NAME(fkc.constraint_object_id),
    Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
            + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
    Referencing_col = QUOTENAME(pc.name),
    Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id)) 
            + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
    Referenced_col = QUOTENAME(rc.name),
    PK = pk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
INNER JOIN (SELECT i.name, fk.[object_id]
  FROM sys.indexes AS i
  INNER JOIN sys.foreign_keys AS fk
  ON i.[object_id] = fk.referenced_object_id
  AND i.index_id = fk.key_index_id
) AS pk
ON pk.[object_id] = fkc.constraint_object_id
ORDER BY Referencing_table, FK, fkc.constraint_column_id;

Istnieją również sztuczki, aby wprowadzić listę kolumn, powiedzmy, listę oddzieloną przecinkami lub poszczególne kolumny, zamiast rozłożyć je na wiersze, ale nie zamierzam inwestować w modyfikowanie tych zapytań, aby wygenerować je, dopóki nie będę wiedział dokładnie, w jakiej formie szukasz