Get list of all stored Procedure & function in SQL server

In this Blog post we are going to discuss how to list all stored procedure in data base. List of Stored procedure & Function has been already in Object Explorer of SQL Server but you can also get list by SQL query, the best way is to use information_schema.

select * 
 from DatabaseName.information_schema.routines 
where routine_type = 'PROCEDURE'

If Procedure or function have suffix like sp_, p_, fn_ then query can filter all these type of procedures.

SELECT  *
FROM    information_schema.routines
WHERE   routine_type = 'PROCEDURE'
AND LEFT(Routine_Name, 3)  IN ( 'sp_', 'p_', 'fn_' )