Extract code from your databases

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.

 

ex1

 

This brings up the Generate and Publish scripts wizard.  Click next to continue.

ex2

 

Since we only need stored procedures, we pick the “select specific database objects” option and then select Stored Procedures.

ex3

 

 

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.

ex4

 

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.

ex5

 

This takes us to the summary page before the process runs.

ex6

 

After running the process you hopefully get all green check marks.

ex7

 

We selected the option to save to a file, so below we see the script.sql file got created.

ex8

 

Opening the file in Notepad, we can see we just extracted all of the stored procedures for our database.

ex9

 

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s