To get the stored procedure scripts

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

To get the stored procedure scripts

Author

Topic

arthiasha
Starting Member

40 Posts

Posted - 2012-10-08 : 05:28:39

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
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-10-08 : 05:54:29

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

arthiasha
Starting Member

40 Posts

Posted - 2012-10-08 : 06:19:07

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-08 : 07:03:49

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources