In SQL Server, how do we find all tables which do not have either of:
- foreign key constraints
- references by other tables' foreign keys
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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;
You can make use of the
sp_fkeys procedure to get the logical foreign key information. But the limitation is you have to execute is by passing the table name as the input parameter. More info for the same can be found at below MSDN library.
Alternatively you can make use of below query to get all the tables from sys.tables and check if the same table object_id exists in the sys.foreign_key_columns.
SELECT tbl.name FROM sys.tables AS tbl LEFT JOIN sys.foreign_key_columns AS fKey ON tbl.object_id = fKey.parent_object_id WHERE fKey.parent_object_id IS NULL
For the sake of completeness, you can also query sys.foreign_keys instead of sys.foreign_key_columns to find tables that are both unreferenced and not referencing others:
SELECT schema_name = s.name, table_name = t.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id LEFT JOIN sys.foreign_keys AS fk ON t.object_id IN (fk.parent_object_id, fk.referenced_object_id) WHERE t.is_ms_shipped = 0 AND fk.object_id IS NULL ;
The query additionally shows each table' schema name, because sometimes systems are built using more than just the default dbo schema.
I have also included the
is_ms_shipped = 0 filter in the query because I am assuming you want to exclude predefined/system tables from the search.
This will also give you the referenced table:
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS Where COLUMN_NAME like '%id' AND COLUMN_NAME <> 'Id' AND DATA_TYPE = 'int' AND TABLE_NAME + COLUMN_NAME NOT IN (SELECT OBJECT_NAME(fk.parent_object_id) + cpa.name FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id INNER JOIN sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id) order by TABLE_NAME, COLUMN_NAME