Copy the Structure of a Database Schema

Database administrators, IT professionals, and others who organize large amounts of information often want to explore database enhancements without risking critical live data. A backup copy of a database structure is useful to:

DatabaseSpy® 2017 is a multi-database query, design, and database compare tool with features that let you efficiently examine and copy database schemas. For this example we’ll use the AdventureWorks database with Microsoft SQL Server, however the same strategy and steps can be performed on any of the popular databases supported by DatabaseSpy.

Suppose you want to duplicate the tables in the AdventureWorks HumanResources schema. You can open AdventureWorks in the Online Browser window, shift-click to select all the tables in the HumanResources schema, then choose the SQL Editor Create option from the right-click context menu.

Copy the Structure of a Database Schema

This selection generates a SQL script and opens it in a new DatabaseSpy® 2017 SQL Editor window. The script contains CREATE statements for each table.

Copy the Structure of a Database Schema

Since you want to duplicate these tables in a new schema, you can use search and replace in the DatabaseSpy SQL Editor to replace all occurrences of HumanResources in the path that specifies each table location.

Copy the Structure of a Database Schema

This replaces HumanResources everywhere in the script, including references to foreign keys that are in some of the CREATE statements.

Next, you can edit the script to add a CREATE SCHEMA statement at the top.

Copy the Structure of a Database Schema

When you execute the script, the Message window alerts you that an error occurred.

Copy the Structure of a Database Schema

You can move the cursor over the error message to identify the statement that failed.

Copy the Structure of a Database Schema

The CREATE statement for the EmployeeDepartmentHistory table failed because it tried to add a foreign key referencing the Shift table, but the Shift table did not yet exist. The Shift table was added by the last statement in the script.

Copy the Structure of a Database Schema

You can highlight the command that failed by clicking the error in the Message window. Whenever a portion of a script is highlighted the SQL Editor Execute button only processes tha selection. Since the Shift table does exist, you can now run the CREATE EmployeeDepartmentHistory statement successfully.

Copy the Structure of a Database Schema

(If you want to save the SQL script to run again later, you could edit the order of the statements so they execute successfully on the first pass.)

Click the Browser Window Update button to display the new tables.

Copy the Structure of a Database Schema