[ACCEPTED]-Stored Procedures to .sql files-stored-procedures

Accepted answer

Score: 15

In SQL Management Studio right click on 3 the database, go to tasks -> Generate Scripts, walkthrough 2 the wizard. One of the pages will let you 1 script each object to its own file.

Score: 6

You can run this select:

select
    O.name, M.definition
from
    sys.objects as O
left join
    sys.sql_modules as M
    on O.object_id = M.object_id
where
    type = 'P'

and you get the 4 name and source code for stored procedures. Propably 3 most easy way, how to put it in files is 2 in some "classic" languge like c#, java, etc 1 ...

Score: 3

If you want to version your entire database, Microsoft 5 has a SQL Server Database Publishing Wizard 4 (you can download it here). The overview says 3 there's direct integration with Visual Studio, but 2 I haven't used it personally to vouch for 1 how good (or bad) it might be.

Score: 2

I wrote a tool I called SMOscript which has an option 3 to create a single .sql file per database 2 object.

It uses SQL Server's SMO library 1 to generate CREATE and DROP scripts.

Score: 1

Try to use Sql Server SMO. An example is included below:

//C:\Program Files\Microsoft SQL Server\{version}\SDK\Assemblies\
using Microsoft.SqlServer;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;

string sqlConnectionString="";
string databaseName="";

var Connection = new SqlConnection(sqlConnectionString);
Connection.Open();
int counter = 0;
var db= new Server(new ServerConnection(Connection)).Databases[databaseName];

foreach (var item in db.StoredProcedures.OfType<StoredProcedure>())
{
    if (item.IsSystemObject ==  false)
    {
        using (TextWriter writer = new StreamWriter(item.Name+".sql", false))
        {
            writer.WriteLine(item.TextHeader + item.TextBody);
        }
    }
}

Score: 1

You can also use the following script to 4 generate selected database stored procedure 3 scripts in a separate .sql file, files will 2 be create by name of procedure.

Using dynamic 1 query and cursor, you can do it like this:

DECLARE @name varchar(100)
DECLARE @Definition varchar(max)
DECLARE @sql varchar(300)
CREATE TABLE TEMPTABLE (ID INT IDENTITY(1,1), def varchar(max))
DECLARE script CURSOR  
FOR
SELECT OBJECT_NAME(SYS.SQL_MODULES.OBJECT_ID), [DEFINITION] FROM 
SYS.SQL_MODULES INNER JOIN SYS.OBJECTS ON
SYS.OBJECTS.OBJECT_ID = SYS.SQL_MODULES.OBJECT_ID 
WHERE SYS.OBJECTS.TYPE='P'
OPEN script
FETCH NEXT FROM script INTO @name, @Definition
WHILE @@FETCH_STATUS = 0 
BEGIN
  FETCH NEXT FROM script INTO @name, @Definition
  INSERT INTO TEMPTABLE VALUES(@definition)
  SET @Sql = ('BCP "SELECT TOP 1 def FROM TEMPTABLE ORDER BY ID DESC" queryout "C:\' + @name + '.sql" -c -T')
  EXEC XP_CmdShell @Sql
END 
CLOSE script
DEALLOCATE script
DROP TABLE TEMPTABLE

Source: stackoverflow.com

[ACCEPTED]-Stored Procedures to .sql files-stored-procedures

IF/ELSE Stored Procedure

MS SQL Server: Check to see if a user can execute a stored procedure

Eliminate and reduce overlapping date ranges

Execute a Stored Procedure in a SELECT statement

Select Average of Top 25% of Values in SQL

How do I pass input parameters to sp_executesql?

Inserting rows into a table with one IDENTITY column only

SQL Server silently truncates varchar's in stored procedures

xml path returns "<" for < and ">" for > while executing query. how to get original value?

What security benefits are provided by using stored procedures to access data?

[ACCEPTED]-Stored Procedures to .sql files-stored-procedures

IF/ELSE Stored Procedure

MS SQL Server: Check to see if a user can execute a stored procedure

Eliminate and reduce overlapping date ranges

Execute a Stored Procedure in a SELECT statement

Select Average of Top 25% of Values in SQL

How do I pass input parameters to sp_executesql?

Inserting rows into a table with one IDENTITY column only

SQL Server silently truncates varchar's in stored procedures

xml path returns "&lt;" for < and "&gt;" for > while executing query. how to get original value?

What security benefits are provided by using stored procedures to access data?

xml path returns "<" for < and ">" for > while executing query. how to get original value?