Generate Scripts for Database Objects with SSMS in SQL Server 2012
Recently my team requested to provide scripts of all objects in a database to create on another instance, so I used ‘Generate and Publish Scripts Wizard’ and created all objects on another database instance. Today we are going to see how we can generate scripts with SQL Server Management Studio. We will take example of AdventureWorks2012 database here.
First go to Object Explorer and right click on AdventureWorks2012 database then go to Tasks and click Generate Scripts… [refer Figure-1]
Figure 1: Generate Script Path
It will open ‘Generate and Publish Scripts’ wizard where you will find introduction about different steps to perform in this wizard which will guide you to generate scripts. [refer Figure-2]
Figure 2: Introduction to Generate Scripts
Basically it is 4 steps process as given below.
Step 1:
You have to select database objects to generate scripts.
Step 2:
You have to specify scripting or publishing options.
Step 3:
Review and confirm your selected objects.
Step 4:
Now you can finally Generate scripts and save it.
Once you finished introduction part then click Next to continue. Now you can select database objects to generate scripts. You have option to select for entire database or specific objects in database. [refer Figure-3]
Figure 3: Choose Database Objects
Following are available objects in this wizard which you can use to generate scripts:
– Tables
– Views
– Stored Procedures
– User-Defined Functions
– Users
– Schemas
After choosing required objects you can continue wizard with Next button.
Now you can select Output Type as ‘Save scripts to a specific location’ and specify location and file name. You also have options like to generate all objects in a single file or generate one object in one file and specify your location and file name then click Advanced button and choose required settings and click Next. [refer Figure-4]
Figure 4: Set Scripting Options
In summary window you can review your selected source, target and options and click Next button. [refer Figure-5]
Figure 5: Summary to Review Selection
Finally it will generate database scripts on the specified location which you can create on other instance easily. [refer Figure-6]
Figure 6: Save Scripts
You can click on Finish button to close the generate scripts wizard.
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
You may also like
Cool article! The only thing I miss is the fact that you can generate insert scripts for tables with this wizard as well. I use this a lot myself to generate insert scripts for let’s say a configuration table.
In the screen shown on figure 4, choose ‘Advanced’ and look for the option called ‘Types of data to script’. Either pick ‘Data only’ or ‘Schema and data’ to include the data in the table in your script as well.
Thank you Marvin, Yes you are right ‘Types of data to script’ is excellent feature which I missed to mention in my article and Video too. I am happy you mentioned it and reader can read your useful comment.