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

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

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

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE WideWorldImportersDW;
SELECT OBJECTPROPERTY(1013578649, 'IsUserTable') AS [IsUserTable];

Result:

+---------------+
| IsUserTable   |
|---------------|
| 1             |
+---------------+

In this case, the WideWorldImportersDW database has an object with the ID provided, and it’s a user-defined table.

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

Result:

+---------------+
| IsUserTable   |
|---------------|
| 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'), 'IsUserTable') AS [IsUserTable];

Result:

+-------------+---------------+
| Object ID   | IsUserTable   |
|-------------+---------------|
| 1013578649  | 1             |
+-------------+---------------+

Example 3 – When the Object is NOT a User-Defined Table

Here’s what happens when the object isn’t a user table.

SELECT OBJECTPROPERTY(402100473, 'IsUserTable') AS [IsUserTable];

Result:

+---------------+
| IsUserTable   |
|---------------|
| 0             |
+---------------+

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

Here it is again using OBJECT_ID().

SELECT 
  OBJECT_ID('Sequences.ReseedAllSequences') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('Sequences.ReseedAllSequences'), 'IsUserTable') AS [IsUserTable],
  OBJECTPROPERTY(OBJECT_ID('Sequences.ReseedAllSequences'), 'IsProcedure') AS [IsProcedure];

Result:

+-------------+---------------+---------------+
| Object ID   | IsUserTable   | IsProcedure   |
|-------------+---------------+---------------|
| 402100473   | 0             | 1             |
+-------------+---------------+---------------+

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