Yesterday, somebody has asked me for a way to Save Stored Procedure text to a text file. I have told him to use management studio for this. But he told me that he needs each stored procedure to be in different file and he also want it to run as a schedule task  I tried and came out with little a bit orthodox solution . But the solution seems very effective. I used SQLCMD for this purpose. The idea is to use SQLCMD and sp_helptext to produce results and then save it to text file using DOS pipe command.

Given below is step by step explanation of what I have done to solve this problem.

NOTE. We are using Advanturework database for this example.

1). Open SSMS.

2). Use following query to generate result.

 select N’sqlcmd -d AdventureWorks -Q “exec sp_helptext ‘ + name + N‘ ” >> c:\sps\’ + name + N‘.txt’ from sys.objects where type like ‘P’

this will geneate result something like :-

sqlcmd -d AdventureWorks -Q “exec sp_helptext uspPrintError ” >> c:\sps\uspPrintError.txt

Here adwanturework is name of database and uspPrintError is name of stored procedure.

NOTE: – if you are not using windows authentication then you can use –U and – P parameter to provide username and login ID also you can use -S parameter for providing server name .Following are other parameters which we can use with SQLCMD.

3).Save result in text file and save it as a BAT file . remember to change its encoding from Unicode to ANSI.

4).Ones saved open file and remove unwanted line from BAT file and save again.

5).create sps folder in c:\ to save generated file.

6).Just execute the BAT file to generate results.

7). Executing BAT file will generate text file contening stored procdure in c:\ sps folder