SQL Server 2012 : How to script all database stored procedures into separate .sql files?

Question

I want to script all the stored procedures from SQL Server 2012 to Visual Studio 2012 as .sql files (in a different project). How do I do that? I want one .sql file for each stored procedure? I get the scripts using the Generate Scripts in Tasks option after right clicking DB in SQL Server 2012. However, the name of the .sql file is spname.StoredProcedure in each case. I want the name to be spname.sql. Again I don't want the file name to be database.spname.StoredProcedure, I want it to be just spname.sql

Answer 1

As @Ivan_Starostin said:

Step one

Step two

Step three

Answer 2

Please check following SQL tutorial showing how to generate a separate script file for each stored procedure in a SQL Server database

The solution uses sp_Helptext stored procedure with SQL BCP command for generating create script for target SP and then create the export .sql file on the file system.

I hope it helps,

Additionally on generate script wizard, if you configure selection options as seen in below picture, you will get the sp name as the output script file name