Introduction

When working with SQL Server, one common requirement is to verify whether a stored procedure exists in a database before executing or calling it from an application (like C#, API, or ETL process).

In this article, we will explore multiple ways to check the existence of a stored procedure, all based on the same concept but giving you different output options depending on your requirement.

Core Concept (Important Understanding)

A stored procedure in SQL Server is stored in system metadata tables such as:

So checking existence means:

Querying system metadata to verify if the procedure name exists in the database.

Method 1: Using INFORMATION_SCHEMA (Readable & Standard)

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_NAME = 'PROCEDURE_NAME';

Output Meaning:

Best for:

Method 2: Using sys.procedures (Recommended for Developers)

SELECT *
FROM sys.procedures
WHERE name = PROCEDURE_NAME';

Output Meaning:

Best for:

Method 3: Using OBJECT_ID (Fastest & Most Used in Code)

IF OBJECT_ID('dbo.PROCEDURE_NAME', 'P') IS NOT NULL
    PRINT 'Procedure Exists'
ELSE
    PRINT 'Procedure Not Found';

Output Options:

Best for:

Method 4: Search Across Schemas (Advanced Check)

SELECT SCHEMA_NAME(schema_id) AS SchemaName, name
FROM sys.procedures
WHERE name = 'PROCEDURE_NAME';

Output:

dbo
PROCEDURE_NAME

Best for:

Method 5: SQL Server Management Studio (UI Method)

Steps:

PROCEDURE_NAME

Best for:

Common Reasons Why Procedure Is Not Found

Even if you think it exists, it may not appear due to:

Best Practice (Real-World Usage)

For applications (C#, API, Dapper), always use:

IF OBJECT_ID('dbo.PROCEDURE_NAME', 'P') IS NOT NULL

Because it is:

Summary of All Methods

Final Takeaway

All methods achieve the same goal:

“Check whether a stored procedure exists”

But the choice depends on your use case: