In SQL Server you can use the OBJECTPROPERTY() function to find out whether or not an object is a stored procedure.

This function accepts two parameters: the object ID, and the property for which you’re checking it for.

Therefore, you can pass the object ID as the first argument, and IsProcedure as the second, and the function will return either a 1 or a 0 depending on whether or not it’s a stored procedure.

A return value of 1 means that it is a stored procedure, 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(466100701, 'IsProcedure') AS [IsProcedure];

Result:

+---------------+
| IsProcedure   |
|---------------|
| 1             |
+---------------+

In this case, the Music database has an object with the ID provided, and it is in fact a stored procedure.

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('Integration.GetLineageKey'), 'IsProcedure') AS [IsProcedure];

Result:

+---------------+
| IsProcedure   |
|---------------|
| 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('Integration.GetLineageKey') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('Integration.GetLineageKey'), 'IsProcedure') AS [IsProcedure];

Result:

+-------------+---------------+
| Object ID   | IsProcedure   |
|-------------+---------------|
| 466100701   | 1             |
+-------------+---------------+

Example 3 – When the Object is NOT a Stored Procedure

Here’s what happens when the object isn’t a stored procedure.

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

Result:

+---------------+
| IsProcedure   |
|---------------|
| 0             |
+---------------+

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

Here it is again using OBJECT_ID().

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

Result:

+-------------+---------------+
| Object ID   | IsProcedure   |
|-------------+---------------|
| 1013578649  | 0             |
+-------------+---------------+

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('NonExistentObject'), 'IsProcedure') AS [NonExistentObject],
  OBJECTPROPERTY(11111111, 'IsProcedure') AS [11111111];

Result:

+---------------------+------------+
| NonExistentObject   | 11111111   |
|---------------------+------------|
| NULL                | NULL       |
+---------------------+------------+

In this case the database contains no objects of that name or ID.