How To Generate Scripts For Database Objects In SQL Server

Introduction

DBAs and developers manage the database environment. When managing databases in different environments, a DBA needs multiple copies. For this, DBA uses various approaches like database backup and restore method and object scripting using generate script option. Sometimes, DBAs and developers don't always need the data and instead need to export and import database objects into the target database.

I know you have a question about the use of generating database object script(s). The answer is, sometimes DBAs have to move the database(s) or database object(s) to another version of SQL Server. And, normally they use the backup and restore approach. But, sometimes, the backup (.bak) file will not be restored due to different versions of SQL Server.

In this tutorial, I am going to explain various methods to generate scripts for various database objects in SQL Server 2019. This detailed article will cover the following topics as follows,

1. Introduction
2. Prerequisites
3. Generate Scripts Wizard
4. Shortcut Method (Without Data)
5. Conclusion

Prerequisites

In order to generate the script(s), make sure that you must have,

• SQL Server Management Studio (SSMS)
• Connect to a server that is running SQL Server.
• A database

(Here, I am using a dummy database for the demonstration purpose which you can download from here).

Method 1 - Using Generate Scripts Wizard

The Generate Script Wizard is generally used to create scripts for transferring databases between instances of the SQL Server Database Engine. You can generate scripts for an entire database, or limit it to specific database objects (viz, database, table, stored procedure, and many more) on an instance of the Database Engine. You can script both the schema and the data by using the "Generate Scripts" method available in SQL Server Management Studio (SSMS). However, scripting tables with data is not ideal with large databases. The generated scripts can be run on another instance of the Database Engine or any other server.

Follow the given below instructions to generate scripts for all database objects.

Step 1

First of all, connect to a server that's running SQL Server.

Step 2

In Object Explorer, expand the "Databases" node.

Step 3

Right-click on the "AdventureWorks2019" Database and select Tasks > Generate Scripts.

Now, complete the "Generate Scripts" wizard by following the below steps,

1. Introduction Page
2. Choose Objects Page
3. Set Scripting Options Page
4. Advanced Scripting Option Window
5. Summary Page
6. Save Scripts Page

Introduction Page

"Generate Scripts" Window with "Introduction" Page will appear on the screen with a detailed description and steps for generating and publishing scripts for database objects. Click the "Next" button to begin the script generation process.

Choose Objects Page

Now, the "Choose Objects" page will open with various options for selecting database object(s). Use this page to choose which database object(s) you want to script.

A) Script entire database and all database objects option

Select this option to generate scripts for the entire database objects (tables, views, stored procedures, functions, triggers, and many more), including a script for the database itself.

B) Select specific database objects option

This option will empower you to generate scripts only for specific database object(s) in the database you choose.

Examples

The examples in this section demonstrate how to script particular database object(s) using Generate Scripts Wizard. Let's see.

1) Scripts Table

Expand the "Tables" option and select the table(s) which you want to script. Now, click the "Next" button to proceed.

2) Scripts Stored Procedures

Expand the "Stored Procedures" option and select the Stored Procedure(s) which you want to script. Now, click the "Next" button to proceed.

Set Scripting Options Page

The "Set Scripting Options" Page will open with various additional advanced options. This page enables you to save scripts to a location of your choice that you can then run scripts against in an instance of the database engine or against a SQL database. Now, specify how the scripts should be saved.

a) Select the "Save as Script file" option. And, choose, "One script file per object" option to generate a separate file for each database object(s).

Note
Select the "One script file per object" option if you have selected more than one database object, otherwise, you can select the "Single script file" option which will generate a single file for all (selected) database objects.

b) Click on the "Advanced" button for more saving options for publishing the script(s).

Advanced Scripting Options Window

The Advanced Scripting Options window comes with a number of advanced options. Specify advanced options by selecting a value to the right of each option from the list of available settings, or you can leave them as they are by default. But, make sure this option "Type of data to script" is set to schema only.

Note
If you need the data with a table script, set the "Types of data to script" option to Schema and Data.

Summary Page

Now, it's time to review the selections you've made. Review your selections that are displayed here, that too by expanding the closed nodes. Click the "Next" button to begin generating scripts.

Save Scripts Page

Congratulations, you have successfully generated the script(s). Click the "Finish" button to complete the process.

Now, navigate to the location that you chose to save your script. All your database script(s) are saved here.

Method 2 - Using the "Script As" Option (Without Data)

This method allows you to script out the database objects (viz, database, table, stored procedure, and many more) by using the "Script Object As" option. In the case of a database, the "Script Database As" option recreates it and its configuration options.

Script Databases

This section describes how to script out the database by using the "Script Database As" option. Note that, this method scripts out only the database configuration options instead of all database objects. Follow the below instructions to proceed.

Step 1

First of all, connect to a server that's running SQL Server.

Step 2

Now, expand the "Databases" node.

Step 3

Right-click on the "AdventureWorks2019" Database and navigate the following path:

Script Database As > CREATE To > New Query Editor Window.

Step 4

A script for creating database queries will open in the window. Now, you can save the script for future use on any other server.

Script Tables

This section describes how to script out tables from the database by using the "Script Table As" option. Note that, only the schema of the table can be generated using this shortcut method. (If you want to include the data with the schema, please refer to Method 1). Follow the below instructions to proceed.

Step 1

First of all, connect to a server that's running SQL Server.

Step 2

Now, Expand the "Databases" node.

Step 3

Expand the "AdventureWorks2019" database node and the "Tables" node.

Step 4

Right-click on the "[Person].[BusinessEntity]" Table and navigate the following path:

Script Table As > DROP And CREATE To > New Query Editor Window.

Step 5

A script for creating table queries will open in the window. Now, you can save the script for future use on any other server.

Script Stored Procedures

This section describes how to script out stored procedures from the database by using the "Script Stored Procedure As" option. Follow the below instructions to proceed.

Step 1

First of all, connect to a server that's running SQL Server.

Step 2

Now, navigate to the stored procedure whose script you want to generate by performing the following steps.

Database > "AdventureWorks2019" Database > Programmability > Stored Procedures.

Step 3

Right-click on the "uspGetBillOfMaterial" stored procedure and navigate to the following path again:

Script Stored Procedures As > DROP And CREATE To > New Query Editor Window.

Step 4

A script for creating stored procedure queries will open in the window. Now, you can save the script for future use on any other server.

References - https://docs.microsoft.com/en-us/sql/

You may also visit my article, Difference Between Delete, Truncate, And Drop Statements In SQL Server.

You can connect with me @

• LinkedIn
• Twitter

Conclusion

In this article, we have discussed various methods to generate scripts of database objects in SQL Server.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.