Tables referenced by Foreign Keys

As part of my project to empty a database I wanted to TRUNCATE tables without Foreign Keys. Here’s a neat script I have not seen elsewhere, that lists the tables that are referenced my at-least one Foreign Key (that therefore could not be truncated)

-- ParentTables.sql
SELECT OBJECT_NAME(referenced_object_id) AS TablesRefdByFKs
FROM sys.foreign_keys

And just for interest here’s the script to list tables that are not parents and may or maybe not children …

-- NonParentTables.sql
SELECT name NonParentTables
FROM sys.tables
where name NOT IN (SELECT OBJECT_NAME(referenced_object_id) FROM sys.foreign_keys)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s