Exporting SQL Server Procedures and Functions to Files

Sometimes it is necessary to export all or some of the stored procedures and functions in a database to files (one file per procedure). This is useful for example if you want to place all your database scripts under source control.

The bcp utility shipped with SQL Server can help. The following query generates one bcp command line per procedure. All commands can then be copied to a batch file and executed from the command line.

In the query below replace and with the appropriate values. Also note that the -T parameter stands for Trusted Authentication, but it is possible to specify a username and password using the -U and -P parameters of the bcp utility (more details and other parameters here).


select 'bcp "SELECT routine_definition from .information_schema.routines where routine_schema = ''' + routine_schema + ''' and routine_name='''
+ routine_name + '''" QueryOut "c:\temp\procs\'
+ routine_schema + '.' + routine_name + '.sql" -c -S -T'
from information_schema.routines