Stored Procedures to .sql files

问题:

Is there a simple process in SQL 2005 for spitting all of my stored procedures out to individual .sql files. I'd like to move them into VSS, but am not too excited by the prospect of clicking on each one to get the source, dumping it into a text file and so on..

回答1:

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

回答2:

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 name and source code for stored procedures. Propably most easy way, how to put it in files is in some "classic" languge like c#, java, etc ...

回答3:

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

回答4:

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

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

回答5:

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);
        }
    }
}

回答6:

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

Using dynamic 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