Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Management Studio Tips – Generating Scripts


By:   |   Last Updated: 2017-07-18   |   Comments   |   Related Tips: More > SQL Server Management Studio

Problem

We continue investigating SQL Server Management Studio (SSMS) features and this tip is devoted to generating scripts in SSMS. Generating scripts of database objects or the whole database is very useful especially when we need to move objects between databases or SQL Server instances. Also, these scripts can be stored for continuous usage or for migration processes to other environments. So, let’s see how scripts can be generated in SSMS.

Solution

There are different ways to generate scripts in SSMS. Should we script a specific object or the list of objects? Should we generate scripts for tables with data or only for the tables’ structures? Should we choose different scripting options such as include or exclude indexes or constraints creation in the script? Will this script run on an earlier version of SQL Server? Is this script needed for copying the whole database like a backup to move it to on an older version of SQL Server?  All these questions will be discussed below and you will be able to decide which scripting option will be useful for a specific task.

Generating scripts of single object in SSMS

We can get scripts of database objects by right-clicking on these objects in SSMS and choose corresponding script to generate. This is useful when we need to get a creation or deletion script for an object structure or for getting templates for modifying data in the table, such as templates for SELECT, INSERT, DELETE and UPDATE scripts:

SSMS script create to functionality

The generated “CREATE TO” script for the “TestTableA” is the following:

SSMS script

In the picture below these are the “SELECT To”, “INSERT To”, “UPDATE To” and “DELETE To” scripts for TestTableA:

SSMS scripts for select insert delete update

It is also possible to generate scripts for a database, but it only creates an empty database structure script without its objects. For stored procedures it is also possible to generate an “EXECUTE To” script, which will be very useful for executing stored procedures in SSMS, especially when there are a lot of parameters and you do not want manually type them. Generating scripts by right-clicking on the object works also for instance level objects such as logins, roles, SQL Server Agent Jobs and so on.

Generating scripts of multiple objects in SSMS

In the example above we generated a script for a specific object. But what can be done if we need to get scripts of multiple objects at once? So, if we need to generate scripts for multiple tables (without data) or deletion we can go to the “View” menu in SSMS and choose “Object Explorer Details” (or call “Object Explorer Details” by pressing “F7”):

ssms object explorer details

When “Object Explorer Details” opens, we will choose the “Tables” node, select tables that need to be scripted by pressing and holding the “Ctrl” key and then right-click to the selection and choose “Script Table as” and then choose the corresponding mode of scripting and finally choose where the generated script will be stored:

ssms object explorer script objects

As a result we will have the following tables’ creation script:

ssms scripted objects

It is possible to do the same thing for other objects: stored procedures, users and so on. Moreover, we can script even instance level objects in the same way. For example we can generate scripts for more than one database creation by choosing SQL Server instance node in “Object Explorer”, then “Databases” and after that choosing databases that need to be scripted.

Using the “Generate Scripts” wizard

None of the above mentioned methods scripts tables with data. Also, sometimes it is necessary to generate scripts of different types of database level objects: tables, stored procedures, users, etc. We can do this using the more flexible “Generate Scripts” wizard. To do this, we need to right-click on the database node in the “Object Explorer”, then select “Tasks” and after that choose “Generate Scripts”:

ssms generate scripts task

The “Generate Scripts” wizard will open:

ssms generate scripts wizard

By pressing “Next” we will go to the next window, where we can choose to script all objects in the database or select specific objects. We are going to illustrate scripting specific objects, so we choose the second option:

ssms generate scripts wizard

We chose TestTableA and TestProcA for generating scripts and pressed the ‘Next’ button. In the next window we can choose the output type, where to store the script and we can choose advanced scripting options by clicking the “Advanced” button:

ssms generate scripts wizard

In the “Advanced Scripting Options” window we can choose a lot of scripting options. For example we can choose the SQL Server version for which the script is being generated. Moreover, we can choose whether or not to script constraints, indexes, only schema, data or both, etc. In our example we choose “Schema and Data” to script the table with data:

ssms generate scripts wizard

The next window reviews your selections and after pressing the “Next” button generation begins and after finishing we can see our script:

ssms generate scripts wizard

Using “Generate Scripts” can be very useful when we need to move database objects to another environment, especially if we need to go to an earlier version of SQL Server.  It is not possible to restore a SQL Server database backup taken on later version of SQL Server to an earlier version. So, to solve this problem, we can generate the entire database script, choose the appropriate version of the destination instance and run this script in the older version SQL Server instance.

Conclusion

Using SQL Server Management Studio, we can generate scripts for database and instance level objects. The methods to get scripts are different starting from the simplest – right-clicking on the object - to a more flexible “Generate Scripts” wizard.

Next Steps


Last Updated: 2017-07-18


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools