Quick way to backup SQL SP and Functions?

Question

I have a long list of SPs (stored procedure) and Functions in my SQL server db. I could save them one by one by right clicking and script XXX to Alter To. Is there any way in TSQL to query all SPs and functions save them to xxx.sql files?

For example, for sp_mySP1, I would like to save it to sp_mySP1.sql which is a text file. The database is too big and I would like save only SPs and functions as a backup of source codes.

Answer 1

In management studio; find the database, right-click, tasks, generate scripts;

next-next-next until you "Choose Object Types". Select "Stored procedures" and "User-defined functions", next, Select All; choose an output; go!

Answer 2

1) Right-click on your Database name in the Object Explorer

2) Select "Tasks > Generate Scripts..." from the Context menu

3) Select your Database in the list and click Next

4) Click Next on the Chose Script Options

5) In Object Types, check Stored Procedures and User-defined functions, click Next

6) Click Select All on the Stored Procedures selection screen, click Next

7) Click Select All on the Functions selection screen, click Next

8) Select 'Script to New Query Window' and click Finish