patternsqlModerate

Find all tables without foreign keys in a database

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tableswithoutallforeigndatabasekeysfind

Problem

In SQL Server, how do we find all tables which do not have either of:

foreign key constraints

references by other tables' foreign keys

Solution

SwapnilBhate's answer will return tables that are referenced by a foreign key. Adding a further LEFT JOIN will exclude these:

SELECT tbl.name 
FROM sys.tables AS tbl
    LEFT JOIN sys.foreign_key_columns AS fKey 
        ON tbl.object_id = fKey.parent_object_id
    LEFT JOIN sys.foreign_key_columns AS rKey 
        ON tbl.object_id = rKey.referenced_object_id
WHERE fKey.parent_object_id IS NULL 
    AND rKey.referenced_object_id IS NULL;

Code Snippets

SELECT tbl.name 
FROM sys.tables AS tbl
    LEFT JOIN sys.foreign_key_columns AS fKey 
        ON tbl.object_id = fKey.parent_object_id
    LEFT JOIN sys.foreign_key_columns AS rKey 
        ON tbl.object_id = rKey.referenced_object_id
WHERE fKey.parent_object_id IS NULL 
    AND rKey.referenced_object_id IS NULL;

Context

StackExchange Database Administrators Q#156359, answer score: 12

Revisions (0)

No revisions yet.