How To Find Stored Procedure Name In SQL Server

As a developer, I could not recall the exact names of stored procedures while working with them. Thousands of stored procedures were in the databases, and searching for and finding them wasn’t easy. Fortunately, there are several ways to locate stored procedures in SQL Server by using their full name, a portion of their name, a text description, or the tables or columns they use. Below, I have discussed how I find stored procedure by name in SQL Server.

How To Find Stored Procedure Name In SQL Server

Using a full name or partial name, we can find the stored procedure by simple queries against tables like sys.procedures and Information_Schema. Routines or comments.

Method 1:Using SQL Server Management Studio

Using the filter setting in SQL Server Management Studio. To find stored procedure name in SQL Server, follow the below steps.

• Expand the database in SQL Server Management Studio’s Object Explorer.
• Extend the programmability folder.
• Select the FILTER option from the right-click menu
• Under the FILTER option. The FILTER settings will appear.

• Enter the search phrase (or stored procedure’s name) under Value in the filter settings window against the property Name and select one of the Operators:
• Equals
• Contains
• Doesn’t Contain

Once you click OK, the stored procedure’s name will be displayed under Object Explorer with a filtered option.

Method 2: Sys Procedures

The procedure name, object ID, and other information, such as the created and modified dates, are contained in the sys. Procedures system table. Thus, if you know part of a stored procedure’s name, you can use this option to find the list of stored procedures with the phrase in their names.

SELECT NAME 
FROM sys.procedures
WHERE name LIKE 'u%'

Method 3: Information Schema Routines

A system information schema view is called INFORMATION_SCHEMA.ROUTINES. This system view allows you to retrieve all information regarding a stored procedure, including its name, definition, source code, database schema, created and modified dates, and more. This view allows you to search for stored procedures that contain text by using either the full or partial name of the procedure. I will now provide you with the query to locate the stored procedure by name.

SELECT
    ROUTINE_NAME,  
    ROUTINE_SCHEMA
FROM
    INFORMATION_SCHEMA.ROUTINES 
WHERE
    ROUTINE_TYPE='PROCEDURE' AND
    ROUTINE_NAME LIKE '%UpdateEmployee%'

Method 4: SYS Comments

It is a system compatibility view. The system view retrieves stored procedures, triggers, views, and other objects from the database.

The SELECT statement retrieves stored procedures’ names and source codes by using the WHERE conditions in sys. Comments.

In the WHERE condition, we check that the stored procedure name is equal to 1 and that it starts within the EMPLOYEE.

SELECT
    OBJECT_NAME(id),
    text
FROM
    sys.syscomments 
WHERE
    OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
    OBJECT_NAME(id) LIKE '%Employee%'
ORDER BY OBJECT_NAME(id)

Method-5: SYS. SQL Modules

The SQL Server SYS.SQL_MODULES is a system object catalog view that fetches the user-defined source code of the stored procedure.

SELECT 
    OBJECT_NAME(object_id) AS STORED_PROCEDURENAME
FROM
    sys.sql_modules
WHERE
    OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND
    OBJECT_NAME(object_id) LIKE '%Employee%'

Conclusion

I hope you got an idea of how to check a stored procedure name in SQL Server. Check all the methods to find the stored procedure name in your database.

Read Also:

• SQL Server Stored Procedure Output Parameter
• SQL Server stored procedure if else
• Find Store Procedure in SQL Server by Table Name