Quick, you need to get all of the code for all of your stored procedures in your database! All you need to do is go to your source control system and get them right? What do you mean you do not have them, well here is how to extract them from the database so you can create a source control baseline.
Using SSMS you can easily extract all of your stored procedures, table create scripts, views and other database objects.
Just right click on your database, click Tasks and then Generate Scripts.
This brings up the Generate and Publish scripts wizard. Click next to continue.
Since we only need stored procedures, we pick the “select specific database objects” option and then select Stored Procedures.
If you want to omit some objects, you can open up the stored procedure object and then deselect the items you do not want extracted.
On the next screen you can choose how you want to save the file. If you are going to be putting the scripts into a source control system, usually the “single file per object” option is what I would pick. This gives you a file for each object, and then you can easily import into your system.
If you click the “Advanced” box, there are plenty of options that you can choose to modify the process. You can choose if the scripts are extracted with 2016 syntax or an earlier version, if the commands are drop and create or if you want to include indexes with your table create statements or approx. 20 other options.
This takes us to the summary page before the process runs.
After running the process you hopefully get all green check marks.
We selected the option to save to a file, so below we see the script.sql file got created.
Opening the file in Notepad, we can see we just extracted all of the stored procedures for our database.
Using SSMS you can now extract the DDL for most of the database objects in your databases and with this information, you can put your objects into a versioning system to keep your development process more organized.