Getting the list of all stored procedures in SQL Server DB

A large production database may have thousands of stored procedures created by the developers. You may need to get the list of all stored procedures for documentation purpose. There are several ways to generate the list of user-defined stored procedures in a database. Let’s see couple of simple methods here.

1. Using SYS.PROCEDURES

SYS.PROCEDURES is an object catalog view has the sub set of SYS.OBJECTS with the object type = P, X, RF, and PC. Using this catalog view you can get the list of all the user defined stored procedures along with its created and modified date and time.

SELECT
    name,
	type_desc,
	create_date,
	modify_date
FROM
   sys.procedures 

2. Using INFORMATION_SCHEMA.ROUTINES

INFORMATION_SCHEMA.ROUTINES is a system information schema view. This system view returns the list of all the procedures and functions in the database. By applying a filter, we can get only the list of procedures. ROUTINES schema view will also provide the details about the stored procedure like, name, definition / source code, database schema, created and modified date, etc.

SELECT
    ROUTINE_NAME, 
    ROUTINE_DEFINITION, 
    ROUTINE_SCHEMA,
    SQL_DATA_ACCESS,
    CREATED,
    LAST_ALTERED
FROM
    INFORMATION_SCHEMA.ROUTINES 
WHERE
	ROUTINE_TYPE = 'PROCEDURE'
GO

Related Articles

• Read about searching and finding a stored procedure by it’s partial name in SQL Server.

Reference

• Read More about sys.procdures at Microsoft Docs.