How to script out stored procedures to files?

Question

Is there a way that I can find where stored procedures are saved so that I can just copy the files to my desktop?

Answer 1

Stored procedures aren't stored as files, they're stored as metadata and exposed to us peons (thanks Michael for the reminder about sysschobjs) in the catalog views sys.objects, sys.procedures, sys.sql_modules, etc. For an individual stored procedure, you can query the definition directly using these views (most importantly sys.sql_modules.definition) or using the OBJECT_DEFINITION() function as Nicholas pointed out (though his description of syscomments is not entirely accurate).

To extract all stored procedures to a single file, one option would be to open Object Explorer, expand your server > databases > your database > programmability and highlight the stored procedures node. Then hit F7 (View > Object Explorer Details). On the right-hand side, select all of the procedures you want, then right-click, script stored procedure as > create to > file. This will produce a single file with all of the procedures you've selected. If you want a single file for each procedure, you could use this method by only selecting one procedure at a time, but that could be tedious. You could also use this method to script all accounting-related procedures to one file, all finance-related procedures to another file, etc.

An easier way to generate exactly one file per stored procedure would be to use the Generate Scripts wizard - again, starting from Object Explorer - right-click your database and choose Tasks > Generate scripts. Choose Select specific database objects and check the top-level Stored Procedures box. Click Next. For output choose Save scripts to a specific location, Save to file, and Single file per object.

These steps may be slightly different depending on your version of SSMS.

Answer 2

Stored procedures are not "stored" as a separate file that you're free to browse and read without the database. It's stored in the database it belongs to in a set of system tables. The table that contains the definition is called [sysschobjs] which isn't even accessible (directly) to any of us end users.

To retrieve the definition of these stored procedures from the database, I like to use this query:

select definition from sys.sql_modules 
where object_id = object_id('sp_myprocedure')

But I like Aaron's answer. He gives some other nice options.