Script out Procedures to seperate files

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

Here is another way to do the same but different file for each procedure. The file name will be the name of the procedure.

declare @sps table(proc_name varchar(100),texts varchar(8000))

insert into @sps
select name,'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')

create table scripts(sps varchar(8000))

declare @texts varchar(1000)
declare @file_name varchar(100)
declare @sql varchar(1000)

select
@texts=min(texts)from @sps

while @texts>''
Begin
        select
@file_name=proc_name from @sps where texts=@texts
        EXEC('insert into scripts(sps) EXEC '+ @texts) insert into scripts(sps) select 'GO'
        select @texts=min(texts)from @sps where texts>@texts 
        set @sql='bcp "select * from yourdb..scripts" queryout "c:\'+@file_name+'.txt" -c' 
        EXEC master..xp_cmdshell @sql
        truncate table scripts
End

drop table scripts

 

Published 26 October 2009 09:34 by Madhivanan

Comments

# re: Script out Procedures to seperate files

14 December 2009 09:45 by sfqfirst

You may add '-T' in the bcp clause,like this

-----------------

set @sql='bcp "select * from yourdb..scripts" queryout "c:\'+@file_name+'.txt" -c -T'

-----------------

otherwise its syntax is not right.

# re: Script out Procedures to seperate files

14 December 2009 14:33 by Madhivanan

sfqfirst,

That is needed only for Trusted Connection

# re: Script out Procedures to seperate files

15 December 2009 01:21 by sfqfirst

Thanks.

I have runned your codes on SQLServer2008,SQLServer2005.And I got this return:

---------------------

User name not provided, either use -U to provide the user name or use -T for Trusted Connection

---------------------

But it was right running on SQLServer2000sp4.