T-SQL Quickies: Foreign Key Constraints abfragen

Hier stelle ich zwei T-SQL Scripts zur Verfügung, mit denen die Fremdschlüsselbeziehungen zwischen Tabellen in SQL Server abgefragt werden können.

Zuerst jedoch ein einfaches Beispiel: In dem Beispiel zeigt das customerId-Feld der Persons-Tabelle auf das id-Feld der Customers-Tabelle. Dieses Beispiel ist sehr einfach - und völlig ausreichend...

Das folgende Script listet alle Tabellen auf, die auf die angegebene Tabelle verweisen.

            DECLARE @tableName NVARCHAR(255)
            
            SET @tableName = N'Customers'

            SELECT
              @tableName AS tableName,
              roc.[name] AS tableField, (CASE WHEN 
                pk.object_id IS NULL THEN 0 ELSE 1 END) AS isPkField,
              t.[name] AS foreignKeyTableName,
              poc.[name] AS foreignKeyField
            FROM sys.foreign_keys fk INNER JOIN 
              sys.objects t ON fk.parent_object_id = t.object_id INNER JOIN 
              sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN 
              sys.columns poc ON fkc.parent_object_id = poc.object_id 
                AND fkc.parent_column_id = poc.column_id INNER JOIN 
              sys.columns roc ON fkc.referenced_object_id = roc.object_id 
                AND fkc.referenced_column_id = roc.column_id LEFT JOIN 
              sys.key_constraints pk ON roc.object_id = pk.parent_object_id
            WHERE fk.referenced_object_id IN (
              SELECT obj.object_id 
              FROM sys.objects obj 
              WHERE LOWER(obj.[name]) = LOWER(@tableName))
          

Das folgende Script findet alle Tabellen, auf die durch die angegebene Tabelle verwiesen wird.

            DECLARE @tableName NVARCHAR(255)

            SET @tableName = N'Persons'

            SELECT
              rt.[name] AS foreignKeyTableName,
              roc.[name] AS foreignKeyField,
              @tableName AS tableName,
              poc.[name] AS tableField
            FROM sys.foreign_keys fk INNER JOIN 
              sys.objects t ON fk.parent_object_id = t.object_id INNER JOIN 
              sys.objects rt ON fk.referenced_object_id = rt.object_id INNER JOIN 
              sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN 
              sys.columns roc ON fkc.referenced_object_id = roc.object_id 
                AND fkc.referenced_column_id = roc.column_id INNER JOIN 
              sys.columns poc ON fkc.parent_object_id = poc.object_id 
                AND fkc.parent_column_id = poc.column_id
            WHERE LOWER(t.[name]) = LOWER(@tableName)
          

Kommentar schreiben