SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

arthiasha
Starting Member

India
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?

senthil_nagore
Flowing Fount of Yak Knowledge

India
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)
begin
select @sql = proc_name from @table where id=@i
exec(@sql)
set @i=@i+1

End


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

arthiasha
Starting Member

India
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

sunitabeck
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?
SELECT OBJECT_DEFINITION(OBJECT_ID)
FROM sys.procedures;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.07 seconds. Powered By: Snitz Forums 2000