by Ross Mack - GUI Computing

One of the difficulties involved in managing a SQL Server database is that certain types of database changes require that you drop and re-create tables. Usually, when you are in the initial stages of defining your database structure, this is not too much of a problem. However, once the database design has settled down a little and all your foreign keys and declarative referential integrity is in place it can be quite painful.

Let's say for example that you have a central table to which several other tables have foreign keys and you want to add a bit field to that table. Well, you can't add a bit field without recreating the table. So, you will need to perform all of the following tasks to achieve this:

• Remove the foreign keys from the other tables.
• Drop the required table.
• Recreate the table with the new field.
• Add the foreign keys back to the other tables.

If you have data in the table you need to drop you will also need to save and recreate that.

This whole task can become extremely arduous for large databases and where there are many foreign keys. It can easily get to a point where you spend a great deal of time recording and destroying and then re-creating foreign keys just so you can spend a couple of minutes changing a table or two.

Faced with this sort of situation (a database with over 120 foreign keys) and needing to modify a few tables I decided to spend a little while coming up with a better way to handle this. That is to say, some way to not have to do it all manually.

The first step in the process seemed to be, to make sure I could restore any foreign keys that I removed. To approach this problem I consulted some SQL manuals, looked at saving information somewhere or doing some other crazy dynamic thing to neatly allow me to restore foreign keys. After some furious research I grabbed a coffee, took a deep breath and decided to use SQL Server's built In 'Generate SQL' functionality to dump the SQL required to create all the foreign keys out to a SQL script. It's an approach that's simple and required no work on my part to implement.

Simply right click on some object in your database (in Enterprise Manager) and select Generate SQL Scripts. You then make sure you have selected all the tables in the database (I find it easiest to just do all of them), then you make sure you the 'Keys/DRI' option selected but you have 'object drop' and 'object creation' turned off. This will generate a script for the selected items (all the tables) that only generates all their foreign key constraints. Save this script somewhere safe.

OK, so now we have a way to create all the foreign keys again, given that we can delete them. At this point I started snooping around the system tables and catalogue stored procedures. I figured the easiest way I could get all the required information about the existing foreign keys was to use the sp_fkeys stored procedure. This stored procedure returnes more than enough information about foreign keys. Certainly enough information for me to be able to destroy them .

The only problem was that I needed to pass a table name to the sp_fkeys stored procedure to get the data about the foreign keys for that table. This simply resulted with me opening another cursor against one of the system tables so that I could get a complete list of the User tables (SELECT name FROM sysobjects WHERE Type = 'U' ORDER BY name). Now that I could extract the information about each foreign key I figured the easiest thing I could do with it would be to place it into a temporary table. This would also allow me review the data I was collecting before I wrote the code to start destroying things.

So, I added a little code to create a table that matched the data returned by the sp_fkeys stored procedure.

CREATE TABLE #tempFK (
   PKTABLE_QUALIFIER varchar(64),
   PKTABLE_OWNER varchar(64),
   PKTABLE_NAME varchar(64),
   PKCOLUMN_NAME varchar(64),
   FKTABLE_QUALIFIER varchar(64),
   FKTABLE_OWNER varchar(64),
   FKTABLE_NAME varchar(64),
   FKCOLUMN_NAME varchar(64),
   KEY_SEQ int,
   UPDATE_RULE int,
   DELETE_RULE int,
   FK_NAME varchar(64),
   PK_NAME varchar(64))

Notice the # sign, denoting this as a temporary table for this stored procedure. I really don't need a large amount of this information, but defining the whole table allowed me to simply select from the stored procedure into the temporary table.

INSERT INTO #tempFK exec sp_fkeys @table

OK, so now I have a complete list of all the foreign keys in the database. All I need to do is use a cursor to traverse each row of this table and use the data I have about it to drop the actual object from the database.

EXEC ('ALTER TABLE ' + @ftable + ' DROP CONSTRAINT ' + @fkey)

Once all the foreign keys are dropped you can more easily make changes to individual tables by dropping them and re-creating them or by truncating them (deleting all their data) and importing a new set of data without breaking constraints. Of course, the important thing to remember is to run the script to create all the foreign keys you disposed of once your maintenance tasks are completed.

The complete code for the Stored Procedure I use to drop foreign keys is included below. Remember: This stored procedure will drop all foreign keys on all user tables in the database. Use with caution and make sure you generate the script to create the foreign keys again before you run this stored procedure.

/****** Object:  Stored Procedure dbo.sp_dropfkeys    Script Date: 03/07/1998 
15:59:07 ******/
CREATE PROCEDURE sp_dropfkeys AS

DECLARE @table varchar(64)
DECLARE @ftable varchar(64)
DECLARE @fkey varchar(64)
DECLARE @sql varchar(255)

CREATE TABLE #tempFK (
   PKTABLE_QUALIFIER varchar(64),
   PKTABLE_OWNER varchar(64),
   PKTABLE_NAME varchar(64),
   PKCOLUMN_NAME varchar(64),
   FKTABLE_QUALIFIER varchar(64),
   FKTABLE_OWNER varchar(64),
   FKTABLE_NAME varchar(64),
   FKCOLUMN_NAME varchar(64),
   KEY_SEQ int,
   UPDATE_RULE int,
   DELETE_RULE int,
   FK_NAME varchar(64),
   PK_NAME varchar(64))

DECLARE curTable SCROLL CURSOR FOR SELECT name FROM sysobjects WHERE Type = 'U' 
ORDER BY name

OPEN curTable

FETCH FIRST FROM curTable INTO @table

WHILE (@@FETCH_STATUS = 0)
BEGIN
   INSERT INTO #tempFK exec sp_fkeys @table
   FETCH NEXT FROM curTable INTO @table
END
CLOSE curTable
DEALLOCATE curTable

DECLARE curFK SCROLL CURSOR FOR SELECT FKTABLE_NAME, FK_NAME FROM #tempFK

OPEN curFK

FETCH FIRST FROM curFK INTO @ftable, @fkey
WHILE (@@FETCH_STATUS = 0)
BEGIN
   EXEC ('ALTER TABLE ' + @ftable + ' DROP CONSTRAINT ' + @fkey)
   FETCH NEXT FROM curFK INTO @ftable, @fkey
END
CLOSE curFK
DEALLOCATE curFK

GO

---

[HOME] [TABLE OF CONTENTS] [SEARCH]