Wednesday, February 28, 2024
HomeProductsSQL Server ToolsHow to Script Tables with Parent Objects in dbForge Studio for SQL...

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

Occasionally, database developers encounter the necessity to conduct both forward and reverse engineering on their databases and specific database objects, which may involve object dependency aspects. For instance, imagine the task of replicating a table from one SQL Server database to another. To accomplish this, you must generate a DDL script for the desired 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 to check object dependency

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 is dependent on. 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 with dependent objects

Thus we get the following script, which takes object dependency into account and where the parent objects will be created first, followed by our dependent table (see the Person.Person table in the screenshot below).

Generated script for both the parent and a dependent table

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 the HumanResources.Employee table is itself a dependent object, which could potentially block the reverse engineering of HumanResources.Employee in another database? Well, we can easily check that possibility in the corresponding Depends On folder.

Check object dependency for the Person.Person table - Depends On

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

Finding dependent objects

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 that will consider all object dependencies. And once again, we right-click the selection and go to Generate Script As > CREATE > To New SQL Window.

Generate Script As for a table and all dependent objects

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

Generated script with object dependency

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 to check the script with object dependencies

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.

Execute the script with object dependencies

That’s it! By the way, accounting for object dependencies in reverse and forward engineering represents only small fraction of the extensive 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