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]
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:
- Look for all foreign key constraints targeting the parent table. Credit goes to this StackOverflow posting for the snippet.
- Temporarily drop all the foreign key relationships.
- Run the script at provided location to re-create our target table.
- Recreate all foreign keys that were dropped in the second step.
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