There are different ways of listing all stored procedures within a database. The first first method is with the INFORMATION_SCHEMA.ROUTINES system view. The INFORMATION_SCHEMA.ROUTINES system view contains one row for each stored procedure and function accessible to the current user in the current database. Since only stored procedures are needed, the ROUTINE_TYPE needs to be filtered out for a value of ‘PROCEDURE’:
SELECT [Routine_Name]
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_TYPE] = 'PROCEDURE'
Another way of getting a list of stored procedures within a database is with the sp_stored_procedures system stored procedure. The sp_stored_procedures system stored procedure returns a list of stored procedures and user-defined functions in the current environment. The list returned by the sp_stored_procedures includes system stored procedures as well. To limit the list to user-defined stored procedures, the owner of the stored procedure needs to be passed as a parameter:
EXECUTE [dbo].[sp_stored_procedures] @sp_owner ='dbo'
The third way of getting a list of stored procedures is by querying the different system views and system tables, namely the sys.procedures, sys.objects, sys.all_objects, and dbo.sysobjects:
SELECT [Name] FROM [sys].[procedures]
SELECT [Name] FROM [sys].[objects] WHERE [type] = 'P'
SELECT [Name] FROM [sys].[all_objects] WHERE [Type] = 'P' AND [Is_MS_Shipped] = 0
SELECT [Name] FROM [dbo].[sysobjects] WHERE [XType] = 'P'