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?
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
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
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;
To get the stored procedure scripts
Posted - 10/08/2012 : 05:28:39
