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

In Case someone comes across this problem, I guess the fastest way to extract all the items (Stored Procedures, Views, User Defied Tables, Functions) is to create a Database project in any solution, then Import everything with Schema Compare and wholaaa you have all the items nicely created in corresponding folders.

Answer 2

I recently came across an issue with programmatically extracting Stored Procedure scripts to file. I started off using the routine_definition approach, but quickly realised that I hit the 4000 character limit... No matter what I tried, I couldn't find a way to get over that hump. (Still interested to know if there's a way around this!)

Instead, I stumbled across a powerful built-in helper; sp_helptext

In short, for the purposes of extracting Stored Procedure Scripts, specifically, sp_helptext extracts each line to a row in the output. ie, 2000 lines of code = 2000 rows in a returned dataset. As long as your individual lines don't exceed the 4000 character limit, nothing will be clipped.

Of course, you can then write the entire table contents to file pretty easily either in SQL, or in my case SSIS.

Answer 3

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 4

It depends on which version of SQL Server you're running. For recent versions, source code for stored procedures is available via the system view sys.sql_modules, but a simpler way to get the source for a stored procedure or user-defined function (UDF) is by using system function object_definition() (which the view definition of sys.ssql_modules uses):

select object_definition( object_id('dbo.my_stored_procedure_or_user_defined_function') )

In older versions, stored procedure and UDF was available via the now-deprecated view system view sys.syscomments.

And in older yet versions of SQL Server, it was available via the system table `dbo.syscomments'

It should be notdd that depending on your access and how the database is configured, the source may not be available to you or it may be encrypted, which makes it not terribly useful.

You can also get the source programmatically using SMO (Sql Server Management Objects). http://technet.microsoft.com/en-us/library/hh248032.aspx

Answer 5

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.