In SQL Server you can use the OBJECTPROPERTY() function to check an object’s type. More specifically, you can check whether it is or isn’t a specific type.

For example, the IsTable property tells you whether or not it’s a table, the IsView property tells you whether or not it’s a view, etc.

This article offers a few basic examples that check whether an object is a table, view, stored procedure, or table-valued function.

Example 1 – Check for Table

Here’s an example that checks if an object is a table.

USE Music; 
SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo.Artists'), 'IsTable') AS [IsTable];

Result:

+-----------+
| IsTable   |
|-----------|
| 1         |
+-----------+

Here, the object name is Artists and the schema is dbo.

In this case, the result is 1, which indicates that the object is in fact a table.

Example 2 – Check for View

Here’s an example that checks if an object is a view.

USE Music; 
SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo.Artists'), 'IsView') AS [IsView];

Result:

+----------+
| IsView   |
|----------|
| 0        |
+----------+

In this case, I’m checking the same object from the previous example, and so we already know it’s not a view. Therefore, the result is 0, which indicates that it’s not a view.

Here’s another example, this time the object is in fact a view:

SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo.RockAlbums'), 'IsView') AS [IsView];

Result:

+----------+
| IsView   |
|----------|
| 1        |
+----------+

Note that I removed the USE Music; part, because I’m already in that database. The OBJECTPROPERTY() only checks schema-scoped objects in the current database.

Example 3 – Conditional Statement

We can take the concept a step further, and incorporate the previous examples into an IF statement. That way we can run a single statement to find out what type the object is.

Below are basic examples that simply output the object type.

Table

DECLARE @TheObject varchar(255) = 'dbo.Artists';
IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsView') AS [IsView]) = 1
  PRINT 'View';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsTable') AS [IsTable]) = 1
  PRINT 'Table';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsProcedure') AS [IsProcedure]) = 1
  PRINT 'Stored Procedure';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsTableFunction') AS [IsTableFunction]) = 1
  PRINT 'Table-valued Function';
ELSE
  PRINT 'Unknown. Maybe add more types to this statement.';

Result:

Table

In this case, the object is a table.

Here are more examples that use the same statement, but with different object types.

View

DECLARE @TheObject varchar(255) = 'dbo.RockAlbums';
IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsView') AS [IsView]) = 1
  PRINT 'View';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsTable') AS [IsTable]) = 1
  PRINT 'Table';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsProcedure') AS [IsProcedure]) = 1
  PRINT 'Stored Procedure';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsTableFunction') AS [IsTableFunction]) = 1
  PRINT 'Table-valued Function';
ELSE
  PRINT 'Unknown. Maybe add more types to this statement.';

Result:

View

Stored Procedure

DECLARE @TheObject varchar(255) = 'dbo.uspGetAlbumsByArtist';
IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsView') AS [IsView]) = 1
  PRINT 'View';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsTable') AS [IsTable]) = 1
  PRINT 'Table';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsProcedure') AS [IsProcedure]) = 1
  PRINT 'Stored Procedure';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsTableFunction') AS [IsTableFunction]) = 1
  PRINT 'Table-valued Function';
ELSE
  PRINT 'Unknown. Maybe add more types to this statement.';

Result:

Stored Procedure

Table-Valued Function

DECLARE @TheObject varchar(255) = 'dbo.ufn_AlbumsByGenre1';
IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsView') AS [IsView]) = 1
  PRINT 'View';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsTable') AS [IsTable]) = 1
  PRINT 'Table';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsProcedure') AS [IsProcedure]) = 1
  PRINT 'Stored Procedure';
ELSE IF (SELECT OBJECTPROPERTY(OBJECT_ID(@TheObject), 'IsTableFunction') AS [IsTableFunction]) = 1
  PRINT 'Table-valued Function';
ELSE
  PRINT 'Unknown. Maybe add more types to this statement.';

Result:

Table-valued Function

You can add more types to the statement to make it more useful. I’ve listed the arguments that OBJECTPROPERTY() accepts on this page. Not all of those are object types though – there are many different properties that you can check for.

For a full explanation of each property, see the Microsoft documentation.