Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to script out stored procedures to files?

Tags:

sql

sql-server

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

like image 333
Hotlansy Httlandy Avatar asked Aug 14 '13 19:08

Hotlansy Httlandy


2 Answers

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.

like image 114
Aaron Bertrand Avatar answered Oct 02 '22 15:10

Aaron Bertrand


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.

like image 29
Michael J Swart Avatar answered Oct 02 '22 17:10

Michael J Swart