Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Call GETDATE Function in a User-Defined Function
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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'

Back to Tip of the Day List Next Tip