Find Stored Procedure by Name in SQL Server

This SQL Server tutorial will teach you how to find the stored procedure in SQL Server by name.

Two methods can be used to find stored procedures in SQL Server by using the procedure name. Here are methods which are used are given below.

• Using different system tables & views
• Using SQL Server Management Studio

Search Stored Procedure by Name in SQL Server

To find the stored procedure use its full name or partisan name, we can simply use the SELECT queries against the following system tables:

• sys. procedures
• information_schema.routines
• syscomments

Method-1: Using SYS.PROCEDURES:

The SQL Server SYS.PROCEDURES is a system view that carries the object Id, procedure name, and other details like modify date, create date, etc.

We can use this option to find the list of procedures with the phrase in their name in order to learn more about the SQL Server stored procedure.

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

With the help of the SELECT statement, we retrieve all records of the NAME column from the sys.procedures system view by the WHERE condition.

In the WHERE condition, we use the LIKE clause on the NAME column to search for stored procedure names starting with the letter u. This will bring all stored procedure names from the system tables to the resultset.

NAME

uspGetBillOfMaterials

uspGetEmployeeManagers

uspGetManagerEmployees

uspGetWhereUsedProductID

uspLogError

uspPrintError

uspSearchCandidateResumes

uspUpdateEmployeeHireInfo

uspUpdateEmployeeLogin

uspUpdateEmployeePersonalInfo

Read: SQL Server stored procedure if else

Method-2: INFORMATION_SCHEMA.ROUTINES:

It is a system information schema view. It’s a system view that is used to store details of the STORED PROCEDURE like source code of procedure, procedure name, database schema, and create and modify date.

This view can be used to search stored procedures by their full or partisan name and the stored procedure that contains the text.

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

With the help of the SELECT statement, we retrieve all records of the ROUTINE_NAME, ROUTINE_DEFINITION, and ROUTINE_SCHEMA from the INFORMATION_SCHEMA.ROUTINES system table by the WHERE condition.

In the WHERE condition, we equalized the ROUTINE_TYPE column as “Procedure” and the ROUTINE_NAME has a name between the “UpdateEmployee”.

If the WHERE condition turns out to be true, then the SELECT statement will retrieve all procedure names, definitions, and table names to the resultset.

ROUTINE_NAME	ROUTINE_SCHEMA
uspUpdateEmployeeHireInfo	HumanResources
uspUpdateEmployeeLogin	HumanResources
uspUpdateEmployeePersonalInfo	HumanResources

Read: Advanced Stored Procedure Examples in SQL Server

Method-3: Using SYS.SYSCOMMENTS:

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

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

By using the WHERE conditions in sys. syscomments, the SELECT statement retrieves the names and source codes of stored procedures.

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

Then we arrange the records of the stored procedure name in ascending order. This resultant result will bring all stored procedure names from a table name to the resultset.

STOREPROCEDURENAMES

uspGetEmployeeManagers

uspGetManagerEmployees

uspUpdateEmployeeHireInfo

uspUpdateEmployeeLogin

uspUpdateEmployeePersonalInfo

Read: Loop in SQL Server stored procedure

Method-4: Using SYS.SQL_MODULES:

The SQL Server SYS.SQL_MODULES is a system object catalogue view. It’s used to fetch 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%'

By using the WHERE conditions in sys.sql_modules, the SELECT statement retrieves the names and source codes of stored procedures.

In the WHERE condition, we checking the object_property is equal to 1 and that object_name contains the name starting within the EMPLOYEES.

If the WHERE condition turns out to be true then it will bring the stored procedure name containing the “employee” with their function name to the resultset.

STORED_PROCEDURENAMES

uspGetEmployeeManagers

uspGetManagerEmployees

uspUpdateEmployeeHireInfo

uspUpdateEmployeeLogin

uspUpdateEmployeePersonalInfo

Read: How to test stored procedure in SQL Server

Method-5: Using SQL Server Management Studio

Using the filter setting in SQL Server Management Studio, you can find the stored procedure by using its partisan or full name. The stored procedure filter settings are as follows.

• 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

• Filter criteria filter the checklist of stored procedures. Extend the Stored Procedures folder. The filter only shows stored procedures that match the criteria

You may also like to read the following SQL Server tutorials.

• SQL Server stored procedure return value
• SQL Server stored procedure modified date
• SQL Server stored procedure parameters

Conclusion

We hope you understand how to find the stored procedure by name in SQL Server. And we have explained in depth the different subtopics.

• Find Stored Procedure by Name in SQL Server using system tables
• Find Stored Procedure by Name in SQL Server using SQL Server Management Studio