Get a List of Stored Procedures Within a Database

You can get the list of Stored Procedure within a database through different ways. 


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.  


SELECT Routine_Name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'


Here we need to get only Stored Procedure, so  ROUTINE_TYPE filtered out for a value of ‘PROCEDURE’.


The second 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.  


EXECUTE [dbo].[sp_stored_procedures] @sp_owner ='dbo'


Here we had passed a parameter to limit the User defined stored procedure. 


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'