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)
ORDER BY name
Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s