How to Find a Stored Procedure in all the Databases?

When working with MS SQL Server sometimes you need to find a stored procedure in the databases and all that you know is the name of the stored procedure that your teammate told you that he had used but couldn’t remember in which database it was. All the information about the stored procedures created is saved in the master database. So, if you have access to the master database, you can run the following query to get information about the stored procedure.

select * 
from 
   sys.procedures 
where 
   name like '%name_of_proc%'

This will tell you in which schema you will find the stored procedure. Even, if you want to look into the code you can do so by running the following query.

select text 
from 
    syscomments c
    inner join sys.procedures p on p.object_id = c.id
where 
    p.name like '%name_of_proc%'

The text field contains the code of the stored procedure.

Let me know in the comments if you find this helpful or if you have any other ways to find the stored procedures.