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.
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.
As a result, we get 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.
Furthermore, there is no table that the foreign key refers to, so it’s no good either.
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.
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).
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.
There, we find more objects that may have their own parent objects. Let’s open all Depends On folders to check it.
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.
And so we get our script. We can see that the objects in it are ordered according to their dependencies.
Now let’s add a few lines to our script in order to create a test database GenerateScriptAsTest with the required schemas.
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.
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!
