Home > Computer Related Pages > Find Dependent Tables In SQL Server

How To Find Dependent Tables In SQL Server

Here's a query that will list all dependent tables and fields of a table you specify. That is, foreign keys and column names. This might be useful if you need to delete a record from the master table and have to delete records from its dependent tables first.

Just replace myTableName in the WHERE clause accordingly.

SQL Query

SELECT
	pt.COLUMN_NAME AS primaryField,
	c.CONSTRAINT_NAME AS keyName,
	fk.TABLE_NAME AS foreignTable, 
	cu.COLUMN_NAME AS foreignField
FROM 
	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
	INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
		c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON
		c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON
		c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
	INNER JOIN (
		SELECT tc.TABLE_NAME, kcu.COLUMN_NAME 
		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
		INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
		ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME 
		WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
	) pt ON pt.TABLE_NAME = pk.TABLE_NAME
WHERE pk.TABLE_NAME = 'myTableName' 
ORDER BY foreignTable ASC;

Output

Here's a sample of the output you'll get: