DEV Community

Cover image for I want to manually delete rows, so tell me about all referenced tables
Adrian Bartosz
Adrian Bartosz

Posted on

I want to manually delete rows, so tell me about all referenced tables

 SELECT 
      COL_NAME (col.referenced_object_id, col.referenced_column_id) as primary_column,
      OBJECT_NAME (fkey.referenced_object_id) AS in_primary_table,

      '1 to N' as [refers],

      OBJECT_NAME (fkey.parent_object_id) AS in_foreign_table,
      COL_NAME (col.parent_object_id, col.PARENT_COLUMN_ID) AS to_foreign_column,

      fkey.name AS foreign_key_name 
FROM 
    sys.foreign_keys AS fkey

    INNER JOIN sys.foreign_key_columns AS col 
    ON col.constraint_object_id = fkey.object_id
WHERE 
    fkey.type = 'F'
    --AND OBJECT_NAME(fkey.referenced_object_id) like 'YourPrimaryTableName'
ORDER BY 
    in_primary_table, in_foreign_table
Enter fullscreen mode Exit fullscreen mode

Result:
Alt Text

Top comments (0)