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 
list of all stored procedures using 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
List of all stored procedures using ROUTINES

Related Articles

Reference

Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.