How to list all SQL Server Stored Procedures?

Introduction to SQL Server’s Stored Procedures listing using the system catalogs and views.

This comprehensive tutorial will guide through the process of how to list all stored procedures in SQL server with increasing complexity as you read on. Stored procedures are paramount in SQL server as they help us execute a sequence of Transact-SQL statements in a single command. As a Microsoft database expert, learning and understanding how to view all SQL server procedures is extremely beneficial.

Get Started by Accessing SQL Server databases.

To start, remember a SQL Server Stored Procedure is a predefined sequence of SQL statements stored on a database server, which can be invoked and executed as a single unit to perform a specific task.

this tutorial, let’s begin with the simplest method of getting all stored procedures in a SQL server database. To connect to the SQL Server instance using SQL Server Management Studio (SSMS), follow these steps:

  1. Connect to the instance
  2. Expand the database
  3. Then expand the Programmability folder
  4. Finally, expand the Stored Procedures folder to view all stored procedures.

1. Understanding SQL Server system tables

Now that we’ve got the basics, let’s dive deeper. By using the sys.procedures catalog view, we can retrieve more detailed information about the stored procedures in our database. Below, an example of how to use the sys.procedures catalog view. It will return all the properties of each stored procedure in the database. It retrieves information such as:

  • name
  • object_id
  • schema_id
  • type
  • type_desc
  • create_date
  • modify_date
  • and many others.
FROM sys.procedures;

2. View All SQL Server Procedures Using Information Schema

For a more user-friendly approach to display the name of the SQL server procedures, we can utilize the INFORMATION_SCHEMA.ROUTINES view. This time, the query returns the name of each stored procedure (ROUTINE_NAME) and the SQL statement that defines the stored procedure (ROUTINE_DEFINITION).


In SQL Server Management Studio, it gives a list of the names and their definition.

T-SQL query to list all the SQL Server Stored Procedures in a database
T-SQL query to list all the SQL Server Stored Procedures in a database

3. Explore T-SQL Procedures with Object Definitions

Another way to list all stored procedures in SQL server is by using the OBJECT_DEFINITION function along with the sys.procedures view. This function is helpful as it allows us to see the text of the SQL Server stored procedures. Here’s how to do it:

FROM sys.procedures pr;

The T-SQL query provided above query will return the name of the stored procedure and its definition.

4. Leverage Dynamic SQL to get all SQL Procedures names

Dynamic SQL is a powerful feature in MS SQL databases that allows us to construct SQL statements dynamically at runtime. We can use dynamic T-SQL to create a stored procedure that will list all stored procedures in a SQL server database. Check out the following example:

CREATE PROCEDURE ListAllStoredProcedures

   SET @SQL = N'SELECT name FROM sys.procedures'
   EXEC sp_executeSQL @SQL


Conclusion about SQL Server Stored Procedures listing

In conclusion, the ability to view all SQL server procedures is a fundamental concept in SQL database management which significantly improves efficiency. The methods provided in this tutorial range from simple to complex to cater to both beginners and advanced SQL users.

Whether you choose to use Management Studio, catalog views, INFORMATION_SCHEMA, OBJECT_DEFINITION, or dynamic SQL, you can effectively list all stored procedures in SQL server.

For further learning, you can check out the official Microsoft documentation on Viewing a Stored Procedure and SQL Server System Stored Procedures.

You can also check of course our other tutorials on SQL Server procedures

Be the first to comment

Leave a Reply

Your email address will not be published.