The OBJECTPROPERTY() function in SQL Server enables you to check an object for a specific property.

You can use this function to check whether an object is a system table or not. To do this, pass the object ID as the first argument, and IsSystemTable as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a system table (1 means that it is a system table, and 0 means that it’s not).

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

SELECT OBJECTPROPERTY(3, 'IsSystemTable') AS [IsSystemTable];

Result:

+-----------------+
| IsSystemTable   |
|-----------------|
| 1               |
+-----------------+

In this case, the object is a system table.

Example 2 – Object is NOT a System Table

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

SELECT OBJECTPROPERTY(1013578649, 'IsSystemTable') AS [IsSystemTable];

Result:

+-----------------+
| IsSystemTable   |
|-----------------|
| 0               |
+-----------------+

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

Here it is again using OBJECT_ID() to get the ID from the object’s name.

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

Result:

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

I also checked to see if the object is a user-defined table, and the result is positive.

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