SQL Server Performance Forum – Threads Archive

Generate Stored Procedure Scripts

Hello Friends, I have designed below scripts to generate SP CREATE scripts. /*****************************************************/ Declare @s_name sysname
Declare @count int Set @count=1 Declare Callstored cursor for
Select name from sysobjects
where xtype=’P’ order by id open callstored Fetch next from callstored into @s_name While (@@Fetch_status=0) Begin
Print @count
set nocount on
exec sp_helptext @s_name
set @[email protected]+1 Fetch next from callstored into @s_name End
set nocount off
Close callstored Deallocate callstored /**********************************************************/ This scripts gives me perfect output and i want to use output of the above scripts to create SP’s in another database but i can’t use because every create statement is followed by Text
—————————————————————————————– Any way i can get output with above line. Also do give me some suggestion on how to attach below statement before every procedure create statement. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[objectname]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[objectname]
GO Please provide your inputs Thanks and Regards
Ravi Kumar

---

Declare @s_name sysname
Declare @count int Set @count=1 CREATE TABLE #results (textback varchar(8000)) Declare Callstored cursor for
Select name from sysobjects
where xtype=’P’ order by id
open callstored Fetch next from callstored into @s_name While (@@Fetch_status=0) Begin
Print ‘– PROC ‘+CAST(@count as varchar(6))
set nocount on
INSERT INTO #results exec sp_helptext @s_name
DELETE FROM #results WHERE textback = ‘Text’ PRINT ‘if exists (select * from dbo.sysobjects where id = object_id(N”[dbo].[‘[email protected]_name+’]) and OBJECTPROPERTY(id, N”IsProcedure”) = 1)
drop procedure [dbo].[objectname]
GO’ SELECT textback as ‘ ‘ FROM #results
TRUNCATE TABLE #results set @[email protected]+1 Fetch next from callstored into @s_name End
set nocount off
Close callstored Deallocate callstored
DROP TABLE #results

---

I think instead of writing your own script, use Generate Script option from EM Anyway, try this also
Declare @s_name sysname
Declare @count int Set @count=1
Declare Callstored cursor for
Select name from sysobjects
where xtype=’P’ order by id open callstored Fetch next from callstored into @s_name While (@@Fetch_status=0) Begin
Print @count
set nocount on
Declare @s varchar(1000)
select @[email protected]_name
if exists (select * from dbo.sysobjects where id =object_id(@s) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
select @s=’drop procedure ‘[email protected]_name
select @s
exec sp_helptext @s_name
set @[email protected]+1 Fetch next from callstored into @s_name End
set nocount off
Close callstored Deallocate callstored Madhivanan Failing to plan is Planning to fail

---

You can also use SQL-DMO. You can get all the scripts here: http://www.nigelrivett.net/ MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>