This article offers seven ways to return all tables that have foreign keys in a database in SQL Server.

Each table is returned just once, regardless of how many foreign keys it may have. This is different to returning all foreign keys, along with their tables. If you want to do that, see 11 Ways to Return Foreign Keys in SQL Server.

All examples here query the same database, and therefore return the same result.

Option 1 – OBJECTPROPERTY() with sys.tables

The first option is to use the OBJECTPROPERTY() function when querying the sys.tables system view.

This function accepts a TableHasForeignKey argument, which will be either 1 or 0 (or NULL). If it’s 1, this means that the table does have a foreign key. A value of 0 means that it doesn’t have any foreign keys. Therefore, we can use this in a WHERE clause to return only those tables where TableHasForeignKey is set to 1.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasForeignKey') = 1
ORDER BY [Schema], [Table];

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Artists |
+----------+---------+

Option 2 – OBJECTPROPERTY() with INFORMATION_SCHEMA.TABLES

This example uses OBJECTPROPERTY() when querying the INFORMATION_SCHEMA.TABLES system view.

SELECT 
  TABLE_SCHEMA,
  TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)),'TableHasForeignKey') = 1 AND
TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

Result:

+----------------+--------------+
| TABLE_SCHEMA   | TABLE_NAME   |
|----------------+--------------|
| dbo            | Albums       |
| dbo            | Artists      |
+----------------+--------------+

Option 3 – OBJECTPROPERTY() with sys.objects

Here’s yet another option that uses OBJECTPROPERTY(). This time I use it when querying the sys.objects system view.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name AS [Table]
FROM sys.objects 
WHERE type = 'U'
AND OBJECTPROPERTY(OBJECT_ID(CONCAT(SCHEMA_NAME(schema_id), '.', name)), 'TableHasForeignKey') = 1
ORDER BY [Schema], [Table]

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Artists |
+----------+---------+

Option 4 – INFORMATION_SCHEMA.TABLE_CONSTRAINTS with DISTINCT

Here’s an example that queries the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system view where the constraint type is FOREIGN KEY. In this case, I also use the DISTINCT clause in order to prevent tables being returned more than once when they have more than one foreign key.

SELECT DISTINCT
  CONSTRAINT_SCHEMA,
  TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

Result:

+---------------------+--------------+
| CONSTRAINT_SCHEMA   | TABLE_NAME   |
|---------------------+--------------|
| dbo                 | Albums       |
| dbo                 | Artists      |
+---------------------+--------------+

Here’s what happens if I remove the DISTINCT clause:

SELECT
  CONSTRAINT_SCHEMA,
  TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

Result:

+---------------------+--------------+
| CONSTRAINT_SCHEMA   | TABLE_NAME   |
|---------------------+--------------|
| dbo                 | Albums       |
| dbo                 | Albums       |
| dbo                 | Artists      |
+---------------------+--------------+

In this case the Albums table has two foreign keys, and so I get two rows for that one table.

Option 5 – sys.foreign_keys with DISTINCT

Here’s another example that uses the DISTINCT clause, but this time I’m querying the sys.foreign_keys system view.

SELECT DISTINCT
  OBJECT_SCHEMA_NAME(fk.parent_object_id) AS [Schema],
  OBJECT_NAME(fk.parent_object_id) AS [Table]
FROM sys.foreign_keys AS fk
ORDER BY [Schema], [Table];

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Artists |
+----------+---------+

And here it is without the DISTINCT clause:

SELECT
  OBJECT_SCHEMA_NAME(fk.parent_object_id) AS [Schema],
  OBJECT_NAME(fk.parent_object_id) AS [Table]
FROM sys.foreign_keys AS fk
ORDER BY [Schema], [Table];

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Albums  |
| dbo      | Artists |
+----------+---------+

Option 6 – sys.foreign_keys with GROUP BY

This one’s similar to the previous example in that it queries the sys.foreign_keys system view. The difference is that, rather than using the DISTINCT clause, it uses the GROUP BY clause instead.

SELECT 
  OBJECT_SCHEMA_NAME(fk.parent_object_id) AS [Schema],
  OBJECT_NAME(fk.parent_object_id) AS [Table]
FROM sys.foreign_keys AS fk
GROUP BY 
  OBJECT_SCHEMA_NAME(fk.parent_object_id), 
  OBJECT_NAME(fk.parent_object_id);

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Artists |
+----------+---------+

Option 7 – OBJECTPROPERTYEX()

This example might be doubling up on some previous examples, but it’s still worth mentioning.

Any of the previous examples that use the OBJECTPROPERTY() function, could easily be rewritten to use the OBJECTPROPERTYEX() function. This function is basically an extension to OBJECTPROPERTY(), and it does everything OBJECTPROPERTY() does and more.

So I could rewrite the first example on this page with the following:

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTYEX(object_id, 'TableHasForeignKey') = 1
ORDER BY [Schema], [Table];

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Artists |
+----------+---------+

One difference you should know about is that these two functions return different return types. OBJECTPROPERTY() returns an int whereas OBJECTPROPERTYEX() returns a sql_variant type.