SQL to Get All the Stored Procedures in a SQL Server

We often come across Scenarios, when we need to find a particular object in SQL Server.
Generally the Objects will be either a Stored Procedure or a Table, so while working on one of this Issue I created a SQL query to address this.

/* SQL to Get All the Stored Procedures in a SQL Server */
/* Author : Sharadendu K Singh */
/* "ip.type = 'p'" -- This statement can be altered to 'u'
instead of 'p' to get the list of User defined Tables */
/* Additionally filters can also be applied at "ip.name like '%Query%' */
--Table Variable to Hold all the DB names in a SQL Server

Declare @DBlist table
(
 pk int identity(1,1),
 name nvarchar(100)
)

--Table Variable to Hold all the Stored Procedure in a SQL Server/DB

Declare @SPNametbl table
(
 name nvarchar(110),
 dbname nvarchar(100)
)

--Get the bnames of all the SQL DB in SQL Server

insert into @DBlist(name)
(
Select name
from sys.databases
)

Declare @pkcur int
Declare @pkmax int
Declare @dbname nvarchar(100)
Declare @Usevariable nvarchar(100)

select @pkmax = max(pk) from @DBlist
set @pkcur = 1

/* Loop through all the Databases and get the List of Stored Procedure */

while @pkcur <= @pkmax
begin
Set @dbname = (Select DO.name from @DBlist DO where DO.pk = @pkcur)
Set @Usevariable = 'USE' + @dbname;
EXEC(@Usevariable)
insert into @SPNametbl(name, dbname)
(
select ip.name, @dbname
from sys.objects ip
where
ip.type = 'p'
)
Select @pkcur = @pkcur +1
end

/* Returns the List of all the Stored Procedure to User */

Select * from @SPNametbl


Hope this Helps!!
For any queries/suggestions mail me @ sksfreedom@gmail.com