Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday 3 December 2019

TSQL to create Stored procedures as separate script in folder-

-------TSQL to create Stored procedures as separate script in folder-----------------------


select name
from sys.procedures


 select   b.name,a.definition into #1
 from sys.sql_modules a inner join sys.procedures  b on a.object_id=b.object_id



 
 while (1=1)
 Begin
 if (select count(*) from #1)=0
break
 Declare @name as varchar(1000)
 Declare @s as varchar(1000)

select top 1 * into ##2 from #1

select top 1 @name=name from ##2

set @s  = ' bcp "  select definition from ##2 " queryout "E:\records\separate SP list\' + @name + '.sql "  -c -t, -T -S'




select @s

exec xp_cmdshell @s

drop table ##2
 
Delete from #1 where  @name=name

 End