SQL Server Management Studio Tips

By: Sergey Gigoyan | Updated: 2017-07-18 | Comments (1) | Related: > 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:

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

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”):

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:

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

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”:

The “Generate Scripts” wizard will open:

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:

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:

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:

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

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

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2017-07-18

SQL Server Management Studio Tips – Generating Scripts