You can use the OBJECTPROPERTY() function in SQL Server to check whether an object is a table-valued function or not.

To do this, pass the object ID as the first argument, and IsTableFunction as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a table-valued function.

A return value of 1 means that it is a table-valued function, and a value of 0 means that it’s not.

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE Music;
SELECT OBJECTPROPERTY(34099162, 'IsTableFunction') AS [IsTableFunction];

Result:

+-------------------+
| IsTableFunction   |
|-------------------|
| 1                 |
+-------------------+

In this case, the Music database has an object with the ID provided, and it’s a table-valued function.

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('ufn_AlbumsByGenre'), 'IsTableFunction') AS [IsTableFunction];

Result:

+-------------------+
| IsTableFunction   |
|-------------------|
| 1                 |
+-------------------+

This is the same object from the previous example.

Here it is again with the ID output separately.

SELECT 
  OBJECT_ID('ufn_AlbumsByGenre') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('ufn_AlbumsByGenre'), 'IsTableFunction') AS [IsTableFunction];

Result:

+-------------+-------------------+
| Object ID   | IsTableFunction   |
|-------------+-------------------|
| 34099162    | 1                 |
+-------------+-------------------+

Example 3 – When the Object is NOT a Table-Valued Function

Here’s what happens when the object isn’t a table-valued function.

SELECT OBJECTPROPERTY(885578193, 'IsTableFunction') AS [IsTableFunction];

Result:

+-------------------+
| IsTableFunction   |
|-------------------|
| 0                 |
+-------------------+

In this case, the database does have an object with that ID, but the object is actually a user table (not a table-valued function), so I get a negative result.

Here it is again using OBJECT_ID().

SELECT 
  OBJECT_ID('Artists') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('Artists'), 'IsTableFunction') AS [IsTableFunction],
  OBJECTPROPERTY(OBJECT_ID('Artists'), 'IsUserTable') AS [IsUserTable];

Result:

+-------------+-------------------+---------------+
| Object ID   | IsTableFunction   | IsUserTable   |
|-------------+-------------------+---------------|
| 885578193   | 0                 | 1             |
+-------------+-------------------+---------------+

I also checked to see if the object is a user-defined table, 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'), 'IsTableFunction') AS [InvalidObject],
  OBJECTPROPERTY(12345678, 'IsTableFunction') 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.