In this SQL Server tutorial, you will understand how to view stored procedures in SQL Server.
As a database developer, you must know how to view a stored procedure, which is helpful in many ways, such as debugging, optimizing, and understanding database schema.
So, here I will explain how to view stored procedures using SSMS and Query. You will learn how to view all the stored procedures in a specific database and also how to view the definition of stored procedures.
Let’s start,
How to View Stored Procedures in SQL Server
There are multiple ways to view stored procedures in SQL Server, and here I will show each way.
First is using the SQL Server Management Studio (SSMS); if you use the SSMS, you can easily view all the stored procedures in a specific database.
Open the SSMS and connect the SQL Server instance, and then you will see the window interface as shown below.
Now, if you look at the left side of the window, there is Object Explorer. In this explorer, expand the Databases folder, and then within that, you will see all the user-created databases, for example, Data and e-commerce, in the above picture.
As you know, stored procedures are stored in specific databases, so here, understand that you have created a stored procedure under the e-commerce databases, so expand this database as shown below.
After expanding the e-commerce databases, you see the different subfolders within it, so your stored procedure exists in the Programmability subfolder, as you can see in the above picture.
To view the stored procedure, explore the Programmability subfolder as shown below.
When you expand the subfolder Programmability, you again see several subfolders; your stored procedures are stored in the subfolder Stored Procedures, as you can see in the above picture.
To view all the stored procedures of the ecommerce database, expand the subfolder Stored Procedures as shown below.
Look, the subfolder stored procedures contains two kinds of stored procedures. The first is the system stored procedure in the subfolder System Stored Procedures, and the second is the created stored procedure you can see like dbo.ProcessOrder etc.
This is how to view the stored procedures in SQL Server using the SQL Server Management Studio.
How to View Stored Procedures in SQL Server through Query
From the above section, you learned how to view stored procedures using the SSMS; here, I will show how to do the same using the query.
So, here, multiple ways exist to view stored procedures in SQL Server through query.
First, open your computer’s query editor or SQL server command prompt, where you can execute the SQL Server query.
View Stored Procedures in SQL Server through Query using sp_helptext
There is a system-stored procedure called sp_helptext, which can help you view the stored procedures. This will not list all the stored procedures. Instead, you can use it to view specific stored procedures.
For example, the ecommerce database contains the ProcessOrder stored procedure. You can view it using the sp_helptext system procedure, as shown below.
EXEC sp_helptext ProcessOrder;
It shows all the statements or how the ProcessOrder stored procedure is created. Well, this sp_helptext is used to view the created stored procedure. Generally, it shows the script of the specified stored procedures.
This is how to view stored procedures in SQL Server through query using sp_helptext.
View Stored Procedures in SQL Server through Query using sys.sql_modules
Next is the sys.sql_modules system view, which contains a row for each object implemented as an SQL module, including stored procedures.
Use the below query to view all the stored procedures in the current database.
SELECT m.definition
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE o.type = 'P'
Look, it returns all the stored procedures in the current database but as definition. This is how you can view all the stored procedures in the current database and the specified stored procedure definition.
Use the below query to view the ProcessOrder stored procedure definition.
SELECT m.definition
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE o.type = 'P' AND o.name = 'ProcessOrder';
From the output, as you can see, the sys.sql_modules view allows us to view the complete definition of the specified stored procedure.
This is how to view stored procedures in SQL Server through query using the sys.sql_modules system view.
View Stored Procedures in SQL Server through Query using INFORMATION_SCHEMA.ROUTINES
The SQL Server has another view called INFORMATION_SHCEMA.ROUTINES contain information about all the stored procedures.
To view all the stored procedures, execute the below query.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';
Now, it is the INFORMATION_SCHEMA.ROUTINES all the stored procedures in the current database that you can see in the query output; look at the column ROUTINE_NAME, which contains procedure names such as usp_GetEmployees, ProcessOrder, etc.
Also, you can view the specified stored procedure using the query below.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'UpdateEmployeeDetails';
So, it returned the two columns, ROUTINE_NAME and ROUTINE_DEFINITION, which contain the stored procedure name and its definition, respectively.
This is how to view stored procedures in SQL Server through query using INFORMATION_SCHEMA.ROUTINES view.
View Stored Procedures in SQL Server through Query using OBJECT_DEFINTION
SQL Server has a function called OBJECT_DEFINTION, which returns the SQL source text of the stored procedure if you know the object ID.
Same as sp_helptext, it returns the script of the stored procedure.
SELECT OBJECT_DEFINITION(OBJECT_ID('ProcessOrder'));
The output returns the definition or script of the ProcesOrder stored procedure. Here, you have to pass the stored procedure name to OBJECT_ID(), which receives the object ID of the specified stored procedure.
Then, this returned object is passed to the OBJECT_DEFINITION() function to view them. That is why the OJBECT_ID() function is passed to the OBJECT_DEFINITION() function.
This is how to view a specific definition of stored procedure in SQL Server.
I hope you understand how to find the stored procedure and its definition in SQL Server.
Conclusion
You learned to view stored procedures in SQL Server using the SSMS and Query.
Where you have used the different stored procedures and functions to view the stored procedure, you also learned to view a specific stored procedure using the sp_helptext and OBJECT_DEFINTION().
You may like to read:
- How to Alter Stored Procedure in SQL Server
- Create SQL Server Stored Procedure Default Parameter
- SQL Server Call Stored Procedure from another Stored Procedure
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.