How to Find Stored Procedure in SQL Server

As a database developer, I reviewed many tables in my organization with stored procedures, views, etc. Sometimes, finding the exact name of the stored procedure I want to work with is difficult. It will throw a message like Could not find stored procedure in SQL Server even though I’m sure it is available.

In this SQL Server tutorial, we will discuss solving the Could not find stored procedure in SQL Server error and try to resolve some related errors.

Could not Find Stored Procedure in SQL Server

The error Could not find stored procedure in SQL server usually occurs when the SQL Server is unable to locate the specified method. The example related to this error is shown in the image below.

In this section, we will discuss some of the solutions that can help overcome this error.

Fix-1: Refresh IntelliSense

If we use SQL Server Management Studio as a first solution, we can try to refresh the IntelliSense local cache.

• For this implementation, click on the Edit option from the menu bar.
• After this, select “IntelliSense” and then click on “Refresh Local Cache.” Alternatively, we can use the “Ctrl + Shift + R” shortcut for the same.

Fix-2: Check the Existence of the Stored Procedure

This error usually occurs when the SQL Server is unable to find the specified stored procedure in the database. So, as a precaution, we can check the existence of that stored procedure in the database.

This process is very easy, while using SQL Server Management Studio.

• First, expand the database directory you want to check for this.
• After this, expand the “Programmability” directory. And then, right-click on “Stored Procedures” and click on “Refresh” to refresh the Object Explorer.

• Next, expand the Stored Procedures directory now, if the procedure exists in that database then, it will be visible under Stored Procedures.

Fix-3: Permission to Execute the Procedure

Now, there can be one more reason behind this error while executing. The reason could be that we are using a role that does not have permission to execute the procedure.

In that case, we need to grant EXECUTE permission for the role of that procedure.

For this implementation, we can use the following syntax.

GRANT EXECUTE ON OBJECT::dbo.YourProc TO YourRole;

Here is a simple example related to the above syntax. In the example, we are granting the EXECUTE permission of the “dbo.MaleStudents” stored procedure to the “DBA” role.

GRANT EXECUTE ON OBJECT:: dbo.MaleStudents TO DBA

Could not find Stored Procedure ‘go’ sql server

Now, in this section, we will discuss one more related error on “Could not find stored procedure“. This error states “Could not find stored procedure ‘GO’“. It simply means that the SQL Server could found the stored procedure with the name “GO“.

The main reason behind this error could be the misuse of the “GO” statement.

In SQL Server, GO is a command that indicates the SQL Server utilities to send the current Transact-SQL batch to the SQL Server instance. All statements entered since the last GO are included in the current batch.

So, we should always use the GO command to specify the Transact-SQL batch for execution. If we use this command in between at the wrong place, then this error may be encountered.

Could not Find stored Procedure ‘sp_dboption’ SQL Server

In this section, we will learn how to resolve the issue of not being able to find the stored procedure ‘sp_dboption‘. This error usually occurs when we try to execute the ‘sp_dboption‘ stored procedure.

In SQL Server, sp_dboption is a system stored procedure used to display or modify database options. However, this procedure is removed from SQL Server 2012 or later versions.

So, if we try to use this stored procedure in SQL Server 2012 or later editions then, we might get this error.

But, don’t worry, there is an alternative for this stored procedure in SQL Server. So, instead of using sp_dboption, we can use the “ALTER DATABASE” command to modify a database. Moreover, we can also use the “sp_replicationdboption” stored procedure if we want to modify the database options associated with replication.

Here is an alternative example for sp_dboption in SQL Server.

ALTER DATABASE [MockData] SET AUTO_SHRINK ON 

ALTER DATABASE [MockData] SET RECOVERY SIMPLE

In the above example, we have enabled the simple recovery mode for the MockData database and the auto shrink option, which will shrink the data and log files for this database.

Could not find Stored Procedure ‘sp_msforeachtable’ sql server

In this section, we will discuss how to resolve the “Could not find stored procedure ‘sp_msforeachtable‘” error. This error occurs only when the SQL Server is unable to find the ‘sp_msforeachtable‘ stored procedure. The complete error message is shown in the image below.

The sp_msforeachtable is a system stored procedure in SQL Server which executes the given statement for each table in the database. And the statement to execute is passed as a parameter to this procedure.

This is a system stored procedure so, it can execute from any database in SQL Server. So, this error usually occurs when your database collation is case-sensitive.

Because the exact name of this stored procedure is “sp_MSforeachtable” whereas we are using “sp_msforeachtable” at the time of execution.

So, the solution to overcome this is error is either by using the exact stored procedure name at the time of execution.

Or check and change the collation for your database from SQL_Latin1_General_CP1_CS_AS to SQL_Latin1_General_CP1_CI_AS. Where CS represents case-sensitive and CI represents case-insensitive.

Here are some of the steps that we can use to check and change the database collation property.

Fix-1: Use the Correct Procedure name

As discussed, the simple solution to overcome this error is by using the exact procedure name. The example for this implementation is shown below.

Use [sqlserverguides]  
exec sp_MSforeachtable 'print "?"' 

The above example simply returns the name of all the tables that are in the sqlserverguides database. The output for this is shown below.

Fix-2: Change the Database Collation

First, run the following query to check the collation for your database.

SELECT Name AS [Database Name], 
       collation_name as [Collation Name]
FROM sys.databases

If your database’s collation is set to case-sensitive, you need to change the collation property to case-insensitive.

ALTER DATABASE database 
   COLLATE SQL_Latin1_General_CP1_CI_AS

In the above query, we need to specify the name of the database for which we want to change that collation. The example for this is shown in the image below.

In this tutorial, we have discussed how to solve the “Could not find stored procedure in SQL Server” error and explained how to resolve some of the related errors, which are given below.

• Could not find stored procedure in sql server
• Could not find stored procedure ”. (Microsoft SQL server error 2812)
• Could not find stored procedure ‘go’ sql server
• Could not find stored procedure ‘sp_dboption’ sql server
• Could not find stored procedure ‘sp_msforeachtable’ sql server

You may also like to read the following articles on SQL Server.

• How To Save Stored Procedure In SQL Server
• What is a stored procedure in sql server
• SQL Server stored procedure modified date
• Try catch in SQL Server stored procedure
• Rename stored procedure in SQL Server
• SQL Server stored procedure parameters