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