Getting all stored procedures from a database

Hardcoder
Posted by in Sql Server category on for Intermediate level | Views : 4911 red flag

Hi all,
Here how you can get all the stored procedure from a database. Hey not only the name but the stored procedures definitions also.
Introduction
Hi all,
Here how you can get all the stored procedure from a database. Hey not only the name but the stored procedures definitions also.
Get by using Cursor

declare @proc_name varchar(100)
declare @str_query nvarchar(MAX)

declare loop_proc cursor
for select [name] from sys.procedures where type='P'
and is_ms_shipped = 0
open loop_proc

FETCH NEXT FROM loop_proc INTO @proc_name
IF @@FETCH_STATUS <> 0

PRINT ' <>'
WHILE @@FETCH_STATUS = 0


BEGIN
SELECT @str_query = 'sp_helptext ' + @proc_name
PRINT @str_query
exec sp_executesql @str_query
FETCH NEXT FROM loop_proc INTO @proc_name
END
CLOSE loop_proc
DEALLOCATE loop_proc

Run these sql statements and view the result in Text mode. You will get all scripts for stored procedures.

Get from sysobjects

This is better option to get all stroed procedures from a database.

SELECT [text]
FROM
sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P'

Conclusion

So you learn how to get all stored procedures from a database, for getting more about sql tricks please visit http://matespoint.blogspot.com/search/label/SQL

 

Page copy protected against web site content infringement by Copyscape

About the Author

Hardcoder
Full Name: Mohammad Irfan
Member Level: Starter
Member Status: Member
Member Since: 6/4/2008 2:49:19 AM
Country: India
hardcoder | www.moirf.com https://www.facebook.com/JobsSoftware
http://matespoint.blogspot.com
- 1982 Born in UP (BASTI now known as Siddharth Nagar) - Started primary education in Village Primary School - Did my MIddle school from Mission (Christian) School - InterMediate from OKM Lar Deoria - Get Gold Medal in BSc Physics(Hons) @ AMU Aligarh - Completed my MCA in 2004 from AMU - Working with HCL Tech.

Login to vote for this post.

Comments or Responses

Posted by: Deeraj on: 7/17/2008
Here is an easy way to query all stored procedures in SQL Server 2005.

Select routine_name,routine_definition,* from information_schema.routines.

HTH.

Best Regards,
Dheeraj.
Posted by: Hardcoder on: 3/26/2015 | Points: 25
Thanks Dheeraj for coming up shorter and cleaner..

Login to post response

Comment using Facebook(Author doesn't get notification)