Tuesday, July 17, 2012

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.  


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.proceduressys.objectssys.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'

No comments:

Post a Comment