You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table has one or more foreign key constraints.

To do this, pass the table’s object ID as the first argument, and TableHasForeignKey as the second argument. The function returns a 1 or a 0 depending on whether or not it has a foreign key constraint.

A return value of 1 means that the table does have a foreign key constraint, and a value of 0 means that doesn’t. A return value of 1 applies regardless of how many foreign keys the table has (as long as it has at least one).

If you want an actual list of the foreign keys, see List All Foreign Keys on a Table in SQL Server.

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE WideWorldImportersDW;
SELECT OBJECTPROPERTY(1493580359, 'TableHasForeignKey') AS [TableHasForeignKey];

Result:

+----------------------+
| TableHasForeignKey   |
|----------------------|
| 1                    |
+----------------------+

In this case, the WideWorldImportersDW database has a table with the ID provided, and it has a foreign key constraint.

Example 2 – Getting the Object ID

If you know the table’s name, but not its ID, you can use the OBJECT_ID() function to retrieve the ID based on its name.

Example:

SELECT OBJECTPROPERTY(OBJECT_ID('Fact.Order'), 'TableHasForeignKey') AS [TableHasForeignKey];

Result:

+----------------------+
| TableHasForeignKey   |
|----------------------|
| 1                    |
+----------------------+

This is the same object from the previous example.

Here it is again with the ID output separately.

SELECT 
  OBJECT_ID('Fact.Order') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('Fact.Order'), 'TableHasForeignKey') AS [TableHasForeignKey];

Result:

+-------------+----------------------+
| Object ID   | TableHasForeignKey   |
|-------------+----------------------|
| 1493580359  | 1                    |
+-------------+----------------------+

Example 3 – When the Table Does NOT Have a Foreign Key

Here’s what happens when the table doesn’t have a foreign key.

SELECT OBJECTPROPERTY(OBJECT_ID('Dimension.City'), 'TableHasForeignKey') AS [TableHasForeignKey];

Result:

+----------------------+
| TableHasForeignKey   |
|----------------------|
| 0                    |
+----------------------+

In this case, the object is a table, it’s just that it doesn’t have a foreign key.

Example 4 – When the Object Isn’t a Table

Here’s what happens when the database does contain an object with the ID, but that object isn’t a table.

SELECT OBJECTPROPERTY(OBJECT_ID('Sequences.ReseedAllSequences'), 'TableHasForeignKey') AS [TableHasForeignKey];

Result:

+----------------------+
| TableHasForeignKey   |
|----------------------|
| NULL                 |
+----------------------+

Example 5 – Object Doesn’t Exist

SQL Server assumes that the object ID is in the current database context. If you pass in an object ID from a different database, you’ll either get a NULL result or you’ll get incorrect results.

SELECT 
  OBJECTPROPERTY(OBJECT_ID('InvalidObject'), 'TableHasForeignKey') AS [InvalidObject],
  OBJECTPROPERTY(12345678, 'TableHasForeignKey') AS [12345678];

Result:

+-----------------+------------+
| InvalidObject   | 12345678   |
|-----------------+------------|
| NULL            | NULL       |
+-----------------+------------+

In this case the database contains no objects of that name or ID, and so I get a NULL result.

You’ll also get NULL on error or if you don’t have permission to view the object.