You can use the OBJECTPROPERTY() function in SQL Server to find out whether or not an object is a foreign key.

To find out whether an object is a foreign key, pass the object ID as the first argument, and IsForeignKey as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a foreign key.

A return value of 1 means that it is a foreign key, and a value of 0 means that it isn’t.

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE Music;
SELECT OBJECTPROPERTY(981578535, 'IsForeignKey') AS [IsForeignKey];

Result:

+----------------+
| IsForeignKey   |
|----------------|
| 1              |
+----------------+

In this case, the Music database has an object with the ID provided, and it’s a foreign key.

Example 2 – Getting the Object ID

If you know the object’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('FK_Albums_Artists'), 'IsForeignKey') AS [IsForeignKey];

Result:

+----------------+
| IsForeignKey   |
|----------------|
| 1              |
+----------------+

In this case I checked the same object from the previous example.

Here it is again with the ID output separately.

SELECT 
  OBJECT_ID('FK_Albums_Artists') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('FK_Albums_Artists'), 'IsForeignKey') AS [IsForeignKey];

Result:

+-------------+----------------+
| Object ID   | IsForeignKey   |
|-------------+----------------|
| 981578535   | 1              |
+-------------+----------------+

Example 3 – When the Object is NOT a Foreign Key

Here’s what happens when the object isn’t a foreign key.

SELECT OBJECTPROPERTY(1525580473, 'IsForeignKey') AS [IsForeignKey];

Result:

+----------------+
| IsForeignKey   |
|----------------|
| 0              |
+----------------+

In this case, the database does in fact have an object with that ID, but the object is actually a view, so I get a negative result.

Here it is again using OBJECT_ID().

SELECT 
  OBJECT_ID('RockAlbums') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('RockAlbums'), 'IsForeignKey') AS [IsForeignKey],
  OBJECTPROPERTY(OBJECT_ID('RockAlbums'), 'IsView') AS [IsView];

Result:

+-------------+----------------+----------+
| Object ID   | IsForeignKey   | IsView   |
|-------------+----------------+----------|
| 1525580473  | 0              | 1        |
+-------------+----------------+----------+

In this case I also checked to see if the object is a view, and the result is positive.

Example 4 – 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'), 'IsForeignKey') AS [InvalidObject],
  OBJECTPROPERTY(12345678, 'IsForeignKey') 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.