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
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.
Read: SQL Server stored procedure if else
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.
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.
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.
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:
- 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
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.