TSQL script to drop foreign keys, re-create table and restore keys

I have run into several situations during database modeling where I had to drop and recreate an existing table.  But the process involved dropping foreign key relationships to this table, creating the table and then re-creating the foreign key relations.  The process felt very manual with no added value, so I decided to do something about it.

First some context.

Assume two tables: “Addresses” and “Zones” created with the following script:


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Addresses]') AND type in (N'U'))
DROP TABLE[dbo].[Addresses]

CREATE TABLE [dbo].[Addresses](
[Id] int IDENTITY(1,1) NOT NULL,
[Name] varchar(50),
[UnitNumber] varchar(10),
[Street] varchar(100),
[City] varchar(100),
[Province] varchar(100),
[PostalCode] varchar(10),
[Country] varchar(50),
[ZoneId] int,
[AddedOn] datetime,
[LastModifiedOn] datetime,
[IsActive] [bit] NOT NULL DEFAULT 1,
CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED (Id),
CONSTRAINT [FK_Addresses_Zones] FOREIGN KEY([ZoneId]) REFERENCES [dbo].[Zones] ([Id]) ON DELETE CASCADE
) ON [PRIMARY]

and


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Zones]') AND type in (N'U'))
DROP TABLE[dbo].[Zones]

CREATE TABLE [dbo].[Zones](
 [Id] int IDENTITY(1,1) NOT NULL,
 [Name] varchar(50),
 [CountryCode] varchar(5), -- The ISO 3166 CountryCode
 [Geometry] geometry,
 [OffPeakStartHour] int,
 [OffPeakEndHour] int,
 [OnPeakStartHour] int,
 [OnPeakEndHour] int,
 [MidPeakStartHour] int,
 [MidPeakEndHour] int,
 [MonthStart] int,
 [MonthEnd] int,
 [TimeZone] int,
 [AddedOn] datetime,
 [LastModifiedOn] datetime,
 [IsActive] [bit] NOT NULL DEFAULT 1,
 CONSTRAINT [PK_Zones] PRIMARY KEY CLUSTERED (Id),
) ON [PRIMARY]

Once created tables, the parent Zones table cannot simply be dropped unless all associated foreign key associations have been dropped.  If not, you get an error similar to this:


Msg 3726, Level 16, State 1, Line 7
Could not drop object 'dbo.Zones' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Line 9
There is already an object named 'Zones' in the database.

from Microsoft SQL Server Studio Management (SSMS).

The error does not tell us specifically which tables currently holds these FOREIGN KEY constraints.  Now, if there was just one of such constraints, as in this example, it is simple enough to find the child Addresses table, drop its foreign key relationship to the parent Zones table, re-create the Zones table, then re-create the keys, using regular means.  Of course this assumes you know that the Addresses table is the only with a child relationship to Zones.

If there are several of such relationships to the Zones table, however, you have to drop each one, create table, then recreate the relationships.  And if your model changes regularly (which it probably will during original system design), this will be a fairly regular process.

Personally, I shy away from manual labor, especially, if it involves repetitive and manual process which teaches me nothing new.   So for this specific problem, I created this simple TSQL sript that will find and drop all foreign key relationships to a table, create the table based on another TSQL script then recreate the keys again.   It goes like this:


if OBJECT_ID ( 'dbo.sp_recreate_table_using_script', 'P' ) is not null
 drop procedure dbo.sp_recreate_table_using_script;
go

create procedure dbo.sp_recreate_table_using_script
 @create_script_name varchar(200),
 @tablename_to_create varchar(100)
as
 declare @foreign_key varchar(100),
 @child_table_name nvarchar(100),
 @child_column_name varchar(100),
 @parent_table_name varchar(100),
 @parent_column_name varchar(100),
 @drop_keys_query varchar(max),
 @recreate_keys_query varchar(max)

declare foreign_key_cursor cursor for

-- Gets all foreign keys to referenced table
with [CTE_FoeignKeys]
 as (
 select
 f.name as ForeignKey
 , OBJECT_NAME(f.parent_object_id) AS ChildTableName
 , COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ChildColumnName
 , OBJECT_NAME (f.referenced_object_id) AS ParentTableName
 , COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ParentColumnName
 from
 sys.foreign_keys as f
 INNER JOIN sys.foreign_key_columns as fc on f.OBJECT_ID = fc.constraint_object_id

 where OBJECT_NAME(f.referenced_object_id) = @tablename_to_create
 ) 

 select * from [CTE_FoeignKeys]

 set @drop_keys_query = '';
 set @recreate_keys_query = '';

 -- http://technet.microsoft.com/en-us/library/ms180169.aspx
 open foreign_key_cursor
 fetch next from foreign_key_cursor into
 @foreign_key,
 @child_table_name,
 @child_column_name,
 @parent_table_name,
 @parent_column_name

 while @@FETCH_STATUS = 0
 begin
 print '... Processing foreign key: ' + @foreign_key

 set @drop_keys_query = @drop_keys_query + ' alter table [dbo].[' + @child_table_name + ']' + ' drop constraint ' + @foreign_key + ';';
 set @recreate_keys_query = @recreate_keys_query +
 ' alter table [dbo].[' + @child_table_name + '] with check add constraint [' + @foreign_key + '] foreign key ([' + @child_column_name + '])' +
 ' references [dbo].[' + @parent_table_name + '] ([' + @parent_column_name +']) on delete cascade; ' +
 ' alter table [dbo].[' + @child_table_name + '] check constraint [' + @foreign_key +'];'; 

 fetch next from foreign_key_cursor into
 @foreign_key,
 @child_table_name,
 @child_column_name,
 @parent_table_name,
 @parent_column_name
 end

 print '... releasing cursor resources... '
 close foreign_key_cursor;
 deallocate foreign_key_cursor;

print 'Executing ' + @drop_keys_query
execute(@drop_keys_query);

print 'Executing ' + @create_script_name
set @create_script_name = 'SQLCMD -S [SQLServerInstance]-d [DatabaseName]-E -i ' + @create_script_name
exec xp_cmdshell @create_script_name 

print 'Executing ' + @recreate_keys_query
execute(@recreate_keys_query);

go

It works as follows:

  1. Look for all foreign key constraints targeting the parent table. Credit goes to this StackOverflow posting for the snippet.
  2. Temporarily drop all the foreign key relationships.
  3. Run the script at provided location to re-create our target table.
  4. Recreate all foreign keys that were dropped in the second step.

This script uses system Stored Procedure Xp_CmdShell which needs to be enabled.

After enabling “Xp_CmdShell” and adding the above Stored Procedure to the database, it can invoke it like so:


declare @create_script_location varchar(100), @table_name varchar(100)
set @create_script_location = 'C:\dev\data\scripts\create-zones-table.sql' ;
set @table_name = 'Zones';

exec sp_recreate_table_using_script @create_script_location, @table_name

Happy Coding.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s