Stored Procedures to .sql files

Question

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..

Answer 1

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 ...

Answer 2

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.

Answer 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.

Answer 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.