Find tables without foreign keys in Azure SQL Database

The query below lists all tables that do not have foreign keys.

See also:

Do table names in your database always make sense? Honestly.

Yeah, ours neither. See what we did about that.

Learn now

Query

select schema_name(fk_tab.schema_id) as schema_name,
    fk_tab.name as table_name,
    '>- no FKs' foreign_keys
from sys.tables as fk_tab
    left outer join sys.foreign_keys as fk
        on fk_tab.object_id = fk.parent_object_id
where fk.object_id is null
order by schema_name(fk_tab.schema_id),
    fk_tab.name

Columns

  • schema_name - name of the schema
  • table_name - name of the table
  • foreign_keys - symbol indicating the lack of FKs

Rows

  • One row: represents one table that doesn't have a foreign key
  • Scope of rows: all tables in a database that don't have foreign keys (do not refer to other tables)
  • Ordered by: schema and table name

Sample results

List of tables in the AdventureWorksLT without foreign keys:

Create beautiful and useful documentation of your Azure SQL

Generate convenient documentation of your databases in minutes and share it with your team. Capture and preserve tribal knowledge in shared repository.

See how it works