SQL Server enables you – in its infinite kindness – to extract all its stored procedure code into one big file, or into one file per stored procedure.
This is very useful if you need to search the stored procedure for a column you modified or want to take a snapshot for a backup.
To do this:
- right-click the database in which the stored procedures exist and choose ‘All Tasks’.
- From the popup menu, choose ‘Generate SQL Script’.
- A window will pop up. Click on the ‘Show All’ button. You will now see all the objects in the database.
- Select the stored procedure check box (or any other part of the code)
- From the ‘Options’ tab, select whether you want one big file or separate files for each stored procedure
- Back in the ‘General’ tab, click OK
- Select where to store the file(s)
- Click ok
