Script out Procedures and Functions - Part 2


 

Part 1 uses information_Schema.routines view and this is based on sp_helptext

 

declare @sps table(texts varchar(8000))

insert into @sps

select 'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')

order by xtype,name

 

create table scripts(sps varchar(8000))

 

declare @texts varchar(1000)

select @texts=min(texts)from @sps

while @texts>''

Begin

EXEC('insert into scripts(sps) EXEC '+ @texts)

insert into scripts(sps) select 'GO'

select @texts=min(texts)from @sps where texts>@texts

End

EXEC master..xp_cmdshell 'bcp "select * from dbname..scripts" queryout "c:\scripts.txt" -c' 

drop table scripts

Published Thursday, December 13, 2007 2:09 PM by Madhivanan
Filed under: ,

Comments

# re: Script out Procedures and Functions - Part 2

Thursday, December 13, 2007 3:58 PM by Adam Machanic

Another way:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/reflect-a-tsql-routine.aspx

# re: Script out Procedures and Functions - Part 2

Friday, December 14, 2007 2:10 PM by Peter

Yet another way...

SELECT OBJECT_DEFINITION (object_id)

from sys.objects

where type in ('P','FN')

# re: Script out Procedures and Functions - Part 2

Friday, December 14, 2007 3:09 PM by Madhivanan

Hi Peter,

OBJECT_DEFINITION works only in SQL Server 2005 or greater version Smile

# update stored procedures | keyongtech

Tuesday, March 3, 2009 3:08 PM by update stored procedures | keyongtech

Pingback from  update stored procedures | keyongtech

# Script out Procedures to seperate files

Monday, October 26, 2009 10:12 AM by Madhivanan

In this post script-out-procedures-and-functions-part-2 , I showed how to script out the procedures in

# Script out Procedures to seperate files

Monday, October 26, 2009 10:18 AM by SQL Server Transact-SQL (SSQA.net)

In this post script-out-procedures-and-functions-part-2 , I showed how to script out the procedures in