You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table is referenced by a foreign key.

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

A return value of 1 means that the table is referenced by a foreign key, and a value of 0 means that it’s not.

Note that the examples presented here don’t list out the foreign keys or their tables or anything like that. They simply return a true/false value that you can use to test whether or not a table is referenced by a foreign key. If you need to list out all foreign keys that reference a given table, see Return All Foreign Keys that Reference a Given Table in SQL Server. The examples in that article list out each foreign key, as well as the foreign key table/s, and the primary key table.

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE WideWorldImportersDW;
SELECT OBJECTPROPERTY(1141579105, 'TableHasForeignRef') AS [TableHasForeignRef];

Result:

+----------------------+
| TableHasForeignRef   |
|----------------------|
| 1                    |
+----------------------+

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

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('Dimension.City'), 'TableHasForeignRef') AS [TableHasForeignRef];

Result:

+----------------------+
| TableHasForeignRef   |
|----------------------|
| 1                    |
+----------------------+

This is the same object from the previous example.

Here it is again with the ID output separately.

SELECT 
  OBJECT_ID('Dimension.City') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('Dimension.City'), 'TableHasForeignRef') AS [TableHasForeignRef];

Result:

+-------------+----------------------+
| Object ID   | TableHasForeignRef   |
|-------------+----------------------|
| 1013578649  | 1                    |
+-------------+----------------------+

Example 3 – When the Table is NOT Referenced by a Foreign Key

Here’s what happens when the table is not referenced by a foreign key.

SELECT OBJECTPROPERTY(OBJECT_ID('Integration.Lineage'), 'TableHasForeignRef') AS [TableHasForeignRef];

Result:

+----------------------+
| TableHasForeignRef   |
|----------------------|
| 0                    |
+----------------------+

In this case, the object is a table, it’s just that it’s not referenced by 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'), 'TableHasForeignRef') AS [TableHasForeignRef];

Result:

+----------------------+
| TableHasForeignRef   |
|----------------------|
| 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'), 'TableHasForeignRef') AS [InvalidObject],
  OBJECTPROPERTY(12345678, 'TableHasForeignRef') 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.