Script tables with parent objects

When you replicate a table from one SQL Server database to another, you might need to generate a DDL script that accounts for all object dependencies—such as user-defined data types and foreign key references. This guide explains how to identify those dependencies and include them in your script.

Scenario

Suppose you need to generate a script for the HumanResources.Employee table from the AdventureWorks2025 sample database and recreate it in another database.

Step 1: Generate a CREATE script for the table

In Database Explorer, locate the HumanResources.Employee table, right-click it, then select Generate Script As > CREATE > To New SQL Window.

Generate Script As command for the HumanResources.Employee table

The script opens in a new SQL window:

The generated script in a new SQL window

However, if you run this script against another database, it will most likely fail. The target database may not contain the objects that HumanResources.Employee depends on, for example, user-defined data types.

User-defined data types in the script

The target database may also be missing the table that the foreign key references.

Foreign key references in the script

Step 2: Include parent objects in the script

To resolve this, under HumanResources.Employee, locate the Depends On folder and select all the objects that the table depends on. Then, right-click the objects and select Generate Script As > CREATE > To New SQL Window.

Generate Script As with dependent objects

The resulting script takes object dependency into account, creating parent objects first, followed by the dependent table.

The generated script that takes object dependency into account

Step 3: Check for other dependencies

Objects listed in the Depends On folder may themselves depend on other objects. Before generating the final script, trace the full dependency chain to make sure no parent objects are missing in the target database.

Open the Depends On folder for each object listed under HumanResources.Employee and check whether it contains further dependencies:

dbo.EmployeeLeaveChangeLog – The Depends On folder is empty. This object has no further parent dependencies and requires no additional tracing.

The Depends On folder for the dbo.EmployeeLeaveChangeLog table

Person.Person – The Depends On folder contains additional objects. Open the Depends On folder for each of those objects to check whether they have dependencies of their own.

The Depends On folders opened for all objects inside the Person.Person Depends On folder, showing their individual dependencies

In this example, none of these folders contains further objects, so the dependency chain ends here, and you can proceed with script generation for the full set of objects. To do that, select all dependent objects, right-click the selection, then select Generate Script As > CREATE > To New SQL Window.

Generate Script As for a table and all dependent objects

The script orders all objects according to their dependencies, so they are created in the correct sequence.

Generated script with object dependency

Step 4: Create a test database and run the script

To test the generated script, you need to modify its header to target a new database instead of the source one.

At the top of the script, replace the existing USE and IF DB_NAME() lines with the following statements, which create a test database and the required schemas, then run the script:

USE master
GO
CREATE DATABASE GenerateScriptAsTest
GO
USE GenerateScriptAsTest
GO
IF DB_NAME() <> N'GenerateScriptAsTest'
  SET NOEXEC ON
GO
CREATE SCHEMA Person
GO
CREATE SCHEMA HumanResources
GO

Note

Before executing the script, make sure that master is selected in Database on the toolbar.

Execute the script to create a test database, required schemas, and a table with object dependencies

After the script runs successfully, open Database Explorer, locate the newly created database, and verify that the table and all required objects were created.

The created table and required dependent objects