How to Script Tables with Parent Objects in dbForge Studio for SQL Server

October 21st, 2022

Now and then, every database developer faces the need to perform both forward and reverse engineering on their databases and individual database objects. Let’s say you have the task of recreating a table from one SQL Server database in another—and in order to do that, you will need to generate a DDL script of the required table.

Here’s a guide on how to do it in the most effortless way using dbForge Studio for SQL Server, one of the world’s most versatile IDEs that cover nearly every aspect of database development, management, and administration.

Download dbForge Studio for SQL Server

Let’s take a rather simple and compelling example: we will generate a script for the HumanResources.Employee table from the AdventureWorks2019 sample database. Our very first step is to find that table in Database Explorer, right-click it, and select Generate Script As > CREATE > To New SQL Window from the shortcut menu.

Generate Script As

As a result, we get the script in a new SQL window.

The script in a new SQL window

However, if we try to execute this script in another database, it will most likely fail, because that database does not contain the required parent objects related to our table—such as User Defined Data Types.

No parent objects

Furthermore, there is no table that the foreign key refers to, so it’s no good either.

No parent objects

There is a solution to that problem: in Database Explorer, under HumanResources.Employee, we find the Depends On folder and select all the objects it contains along with our table. Then, just like we did previously, we right-click them and select Generate Script As > CREATE > To New SQL Window from the shortcut menu.

Generate Script As

Thus we get the following script, where our parent objects will be recreated first, followed by our table (see the Person.Person table in the screenshot below).

Generated script

Now let’s go a little more in-depth by examining the abovementioned Person.Person table. It is the parent object of the HumanResources.Employee table. But what if it has its own parent objects that may eventually block the reverse engineering of HumanResources.Employee in another database? Well, we can easily check that possibility in the corresponding Depends On folder.

Person.Person - Depends On

There, we find more objects that may have their own parent objects. Let’s open all Depends On folders to check it.

Opening the remaining Depends On folders

This time we’re lucky—there are no further parent objects in these folders. That means we can finally select the remaining objects and proceed to generate our script. And once again, we right-click the selection and go to Generate Script As > CREATE > To New SQL Window.

Generate Script As

And so we get our script. We can see that the objects in it are ordered according to their dependencies.

Generated script

Now let’s add a few lines to our script in order to create a test database GenerateScriptAsTest with the required schemas.

Creating a test database

Finally, let’s execute our script and see that our database has been successfully created. Just to make sure everything’s correct, we can check the object tree of our new test database in Database Explorer.

New test database

That’s it! By the way, reverse and forward engineering are only a tiny bit of the capabilities that dbForge Studio for SQL Server can offer. Here are a few things that constitute the focus of our IDE:

  • Advanced SQL code completion, refactoring, and formatting
  • Comparison and synchronization of database schemas and objects
  • Table data comparison and deployment of changes
  • Integrated version control
  • Visual database design and query building
  • T-SQL debugging
  • Generation of meaningful data for testing
  • Generation of comprehensive database documentation
  • Data export and import with up to 14 supported formats
  • Database administration

Download the Studio for a free 30-day trial and see all of them in action!

Download dbForge Studio for SQL Server

Comments are closed.