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
Tags: t-sql, sql_server, script,