Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 To get the stored procedure scripts
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

40 Posts

Posted - 10/08/2012 :  05:28:39  Show Profile  Reply with Quote
Hi experts,
I have a scenario in which i had executed getting the list of all stored procedures in a database. Now with this as input i need to get the scripts of all these stored procedures as output
Here is the query for which i have got the list of all stored procedures.

select 'exec'+' '+'sp_helptext'+ ' ' + name from sys.all_objects where type = 'P'
and object_id > 0

How do i get the scripts of these procedures using the same query as input?

Flowing Fount of Yak Knowledge

1007 Posts

Posted - 10/08/2012 :  05:54:29  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Try something like this

Declare @table table (id int identity(1,1),proc_name varchar(500))
declare @no_of_proc int
Declare @i int
Declare @sql varchar(500)

insert into @table
select 'sp_helptext '''+name +'''' from sys.sysobjects where type='p'
set @no_of_proc = @@rowcount

set @i=1

while (@i<=@no_of_proc)
select @sql = proc_name from @table where id=@i
set @i=@i+1


Senthil Kumar C
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

Starting Member

40 Posts

Posted - 10/08/2012 :  06:19:07  Show Profile  Reply with Quote
I dont need to create any tables actually.
I should execute it with a single query of getting the scripts of all stored procedures with the stored procedures names as input and extract the scripts from it as output
Go to Top of Page

Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  07:03:49  Show Profile  Reply with Quote
Not 100% clear to me what you are trying to do using the "exec sp_helptext", but can you see if object_definition function will give you what you are looking for?
FROM sys.procedures;
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000