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
Asked
Active
Viewed 2.6k times
9
-
1go to SSMS, right click database, choose scripting tasks, check your SPs and set up scripting options – Ivan Starostin Mar 01 '16 at 09:54
-
Possible duplicate of [Script all stored procedures in Management Studio 2005](http://stackoverflow.com/questions/175703/script-all-stored-procedures-in-management-studio-2005) – Liesel Mar 01 '16 at 10:24
-
I want one .sql file for each stored procedure, and almost get it but the name of the files is spname.StoredProcedure, I want it to be spname.sql – Mar 01 '16 at 12:05
2 Answers
9
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

Eralper
- 6,461
- 2
- 21
- 27
-
I tried the above way to script @Eralper , but I get an error -- The text for object 'spname' is encrypted. – Mar 01 '16 at 12:33
-
This is because that stored procedure named "spname" is encrypted. It is not possible to view contents of that procedure. I'm not sure if there is a way to see it. Can you see its content manually? – Eralper Mar 01 '16 at 14:15
-
-
After your comment I tried the dbForge SQL Decryptor tool which worked great :) and free to download and use: https://www.devart.com/dbforge/sql/sqldecryptor/ – Eralper Mar 03 '16 at 09:22
7

Andrey Morozov
- 7,839
- 5
- 53
- 75
-
Although the question titled as single sql file, when read carefully Anil wants one file for each stored procedure – Eralper Mar 01 '16 at 11:35
-
I want one .sql file for each stored procedure, and almost get it but the name of the files is spname.StoredProcedure, I want it to be spname.sql – Mar 01 '16 at 11:39
-
Is somebody going to answer my question? I don't want the file names to be dbname.spname.Stored Procedure. I want the names to be spname.sql. – Mar 01 '16 at 12:22
-
Hi Anil, I missed the option "Single file per object" on "Files to generate". If you select that option and provide the path only below you will get what you expect. – Eralper Mar 01 '16 at 14:18
-