How to script out stored procedures to files?
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.
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.