SQL Server 2000 Script generation and foreign key relations

This document describes generating the script to create or drop tables, their primary key and foreign key relations.

SQL Server 2000 Script generation.
Scripting only a part of your database.
Scripting the relations only.
Dropping tables in the correct order.
 

SQL Server 2000 Script generation.

To create a script from an existing database, right click the database in SQL Server Enterprise Manager:

Fig 1. Starting the SQL Script generation.

The Generate SQL Scripts dialog appears:

Fig 2. The Generate SQL Scripts dialog, "General" tab.

Select the tables to be scripted and press "Add".
Next, select the Formatting tab:

Fig 3. The Generate SQL Scripts dialog, "Formatting" tab.

We selected "Generate the Create <object>" as well as "Generate the Drop <object>" options.
That way, the objects are dropped if they already existed, and then created again.

Next,select the "Options" tab:

Fig 4. The Generate SQL Scripts dialog, "Options" tab.

We left the security scripting options blank because we usually do this in a separate file (to make a distinction between the test- and production  scripting files)
After setting all options required, you can go back to the "General" tab and press "Preview" to preview your script.

Scripting only a part of your database.

When you script all tables in your database, the tables and their relations are automatically dropped and created in the correct order.
When you only script only one or some tables, you have to make sure you don't lose any relations.

Lets have a look at this example:

Suppose you've scripted these tables separately.
The script for the StockOrders table looks like this: CreateTableStockOrders.
The script for the Stocks table looks like this: CreateTableStocks.

When executing CreateTableStockOrders, the table Stockorders is dropped, created and the relation is created.
Result:

When executing CreateTableStocks, the relation is deleted, and table Stocks is dropped and created.
Result:

It is obvious that the script CreateTableStocks has to drop the relation first, otherwise the table Stocks cannot be dropped because something is still pointing to it.
However, you are left with a missing relation. You will have to recreate by hand.

When generating scripts this way for one table, the foreign key relation is:

• created together with the table that contains the foreign key (in this case, table StockOrders with foreign key FK_StockCode).
• dropped together with the table that contains the primary key (in this case, table Stocks with primary key PK_StockCode)

Scripting the relations only.

When you want a script that recreates all relations:

• Select all tables on the "General" tab.
• "Generate the Drop <object>" on the "Formatting" tab.
  This makes sure that the relations are dropped in the script.
• Select "Generate Primary keys, Foreign keys..." on the "Options" tab.
• In the generated script, delete the dropping of the tables and the creation of the primary keys.

Dropping tables in the correct order.

Of course, when you drop tables, they have to be dropped in the correct order.
Tables that are still pointed to, cannot be dropped. So you have to drop the tables which are referring to other tables first:

1. First drop the tables with the foreign keys (tables attached to eternity symbol (00-------->))
2. Then drop the tables with the primary keys (tables attached to key (<--------00))

To generate a script that drops your tables in the correct order, use the script generation method described above, and just select the "Generate the Drop <object>" in the "Formatting" tab of the Generate SQL Scripts dialog.