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
