Generating script for Stored procedures and views in SQL Server 2012
Today I have got a question from one of the beginner DBA. He was actually asked to get “CREATE SCRIPT” for 35 stored procedures and 15 views from one of the database. He started trying to get script for each object one by one and as he doesn’t have clue how to get the task done in a single go.
It may sounds silly for the experienced people but I believe this blog can help the beginners. Now let’s see how to generate script for multiple objects in a database – SQL Server.
To demonstrate this I have created few dummy stored procedures and views.
Now let’s see how to take the “CREATE SCRIPT” for all selected objects.
Right click on Database name (My database name is “udayarumilli”) , click on “Tasks” and click on “Generate Scripts”
A new window opens with the introduction page just click on Next button.
There are two options available,
“Script entire database and all database objects” and
“Select specific database object”.
As we are looking to script the specific objects select the second option and then select the list objects those needs to be script out. To do that expand the object type node and select the required objects. For example to select the “Test_v_1” expand the object type “Views” and select the required view “Test_v_1”.
Once you selected required objects click on next button. It takes you to the next section “Scripting Options”.
There are different save options available I usually choose “Save to New Query Window”. Apart from this we have two more save options “Save to File” and “Save to Clipboard” we can choose either of these depends on the requirement.
Now on the same page click on “Advanced” button to change the scripting configurations.
Once you click on “Advanced” button there will be a new window opens and there you find two group of options “General” and “Table/View Options”. In all of them we usually concentrate on below options.
“Script DROP and CREATE” : There are three options available depends on requirement we can choose one option whether script required for dropping objects, creating objects or drop and creating the object.
“Script for Server Version”: Depends on which server we are going to run this script we can choose the server version from SQL Server 2000 to 2012”.
“Script USE Database”: Depends on whether we require this statement at the top of the script or not we can choose “True” or “False”. Usually the database names similar at source and destination so we choose “True”.
Click on “OK” and then “Next” button. It takes you to the “Summary” phase. Cross check the objects selected and configure options and click on “Next” button.
You can check the script at new query window that was generated by “Script Generator”.
You can check the report on object wise. This report is really useful when we do script for bulk number of objects.
Save the report to your local and open it in internet explorer as below.
Go to the query window; once you check the script execute it at the destination machine or save it to a file.
