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
- Read More about sys.procdures at Microsoft Docs.