How to Script Database Objects with SSMS and SQL Developer

By: Pablo Echeverria | Updated: 2022-11-09 | Comments | Related: > SQL Server and Oracle Comparison

Problem

There are multiple ways to export an entire database or a partial database and in this article, we will look at how this can be done in Oracle and also in SQL Server.

Solution

In this tip, we will demonstrate how to export a database from both Oracle and SQL Server by showing the different screens of the respective wizards and discussing what options are offered.

Export Objects and Data from Oracle

Although it's common to perform a database export using "expdp", this tip will show how to do it using SQL Developer.

First, you need to launch SQL Developer:

Connect to your Oracle database:

Next, go to the toolbar, select "Tools" and then "Database Export" to deploy the Export Wizard:

Step 1: Source/Destination

This is the first screen you will see and you can see there are many options outlined below.

Export DLL

Add BYTE keyword: The column length is specified in bytes (not in characters).
Drops: Check to include the "drop" command.
Show Schema: Uncheck this when creating the objects under a different schema.
Add Force to Views: It will compile the views regardless of the dependent objects or permissions.
Grants: Uncheck to not include "grant" permissions.
Storage: Uncheck when creating the objects with different tablespace definitions.
Cascade drops: Check to include "drop" command for dependent objects (child).
Partitioning: Uncheck when removing partitioning.
Tablespace: Uncheck when creating a different tablespace definition.
Dependents: Check to include "create" for dependent objects (child).
Pretty Print: Uncheck to reduce the file size (not human-friendly).
Terminator: Uncheck to remove the carriage return at the end of each statement.
Version: COMPATIBLE to use the database compatibility level, LATEST to use the database version, or select version 10.1, 10.2, 11.1, 11.2, 12.1 or 12.2.

Export Data

Format: csv, delimited, xlsx, xls, excel.xml, fixed, html, insert (default), json, json-formatted, loader, pdf, t2, text, xml.
Line Terminator: platform default, environment default (default), Unix/Mac LF, Windows CR LF, CR, {EOL}.
Show Schema: includes owner to avoid errors with duplicate names.
Include Commit Every N Rows: check only if required to reduce undo/redo size with large tables.
Save As: single file, separate files, type files, separate directories, worksheet, clipboard.

Click Next.

Note: the navigation is the same for screens: back, next, finish and cancel.

Step 2: Types to Export

Choose the object types to export:

Step 3: Specify Objects

If you want to export just a few objects, you can choose specific ones to export. Click "More" to choose from different schemas and select different object types, and note the button will change from "More" to "Less":

Step 4: Specify Data

If you want to export just a few rows, you can use the specify data option to export using filters. You can filter objects, columns and use a "where" clause:

Step 5: Export Summary

Review a summary of what will be exported:

If everything looks good, click Finish to export.

Export Objects and Data from SQL Server

This section will show how to export a database using SQL Server Management Studio.

First, launch SSMS:

Connect to the SQL Server instance:

Then, right-click on a database, select "Tasks" and "Generate Scripts":

Step 1: Introduction

Just click Next on the introduction screen.

Note: the navigation is the same on all screens: previous, next, finish and cancel.

Step 2: Choose Objects

Choose to script entire database and all objects or select specific database objects. If you select specific database objects you will need to choose each object you want to export:

Step 3: Set Scripting Options

Specify how scripts should be saved:

Save as notebook
Save as script file: single script file, one script file per object, file name, overwrite, Unicode or ANSI
Save to clipboard
Open in new query window

Click Advanced in the upper right of the window for more options. This is an important step in the process.

Below are the Advanced Scripting Options that need clarification:

Convert UDDTs to Base Types: User-defined data types.
Include Descriptive Headers: Add comments.
Include Scripting Parameters Header: Add a header describing the selected options.
Script Bindings: For default and rule objects.
Script Defaults: Include default options explicitly.
Script for Server Version: 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017, 2019.
Script for the Database Engine Edition: Azure SQL Database Managed Instance, Azure SQL Edge, Enterprise, Express, Personal, Standard, Stretch Database.
Script for the Database Engine Type: Azure SQL Database, stand-alone.
Types of Data to Script: Schema only, data only, both.

There are several more options as shown below.

Step 4: Summary

On the next screen, the summary of selections is displayed:

Step 5: Save Scripts

And on the last screen, you will need to wait for the export to complete, then you can open or save the report and finish the wizard:

The wizard shows you what is being done.

Conclusion

In both of the Oracle and SQL Server wizards, you can export schema only, data only, or both. Also, you can include drop and create statements, script grants, script-dependent objects, script for a lower database version, script permissions, etc.

In Oracle, you can also script things if you need to change the database configuration when importing it into another database: change schema, storage, tablespace, partitioning, etc. Also, in Oracle, you can script inserts for very large databases doing a commit every N rows.

In Oracle, you can filter data while creating the export, but in SQL Server you need to generate the schema script only and then use the "Import and Export wizard" if you want to filter the data.

Next Steps

Below are some links about scripting SQL Server databases:

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

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

View all my tips

Article Last Updated: 2022-11-09

SQL Server vs Oracle Exporting Database Objects with SSMS and SQL Developer