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 13 December 2007 14:09 by Madhivanan
Filed under: ,

Comments

# re: Script out Procedures and Functions - Part 2

13 December 2007 15:58 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

14 December 2007 14:10 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

14 December 2007 15:09 by Madhivanan

Hi Peter,

OBJECT_DEFINITION works only in SQL Server 2005 or greater version Smile

# update stored procedures | keyongtech

03 March 2009 15:08 by update stored procedures | keyongtech

Pingback from  update stored procedures | keyongtech

# Script out Procedures to seperate files

26 October 2009 10:12 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

26 October 2009 10:18 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