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

sql sql-server visual-studio-2012 sql-scripts generate-scripts
user5821224 picture user5821224 · Mar 1, 2016 · Viewed 18.1k times · Source

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

Eralper picture Eralper · Mar 1, 2016

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,

enter image description here

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

enter image description here

Related questions

SQL Server 2012 installation Reporting Services Catalog error

I'm installing SQL Server 2012 at the moment and when I was about to run the installation, this error pops up: On clicking the first failed test, which is "Reporting Services Catalog Database File Existence", this is what i get: On …

Read More
Error in SQL script: Only one statement is allowed per batch

I have 4 sql scripts that I want to run in a DACPAC in PostDeployment, but when I try to build the VS project for 3 of them I get this error: Only one statement is allowed per batch. A batch separator, …

Read More
How do I connect to an older sql database using LocalDb in VS 2012?

I am trying to construct a connection string to a 2008 SQL Express database file in VS 2012. I am getting an error dialog: This database file is not compatible with the current instance of SQL Server. To resolve this issue, you …

Read More