1 SET NOCOUNT ON; 2  3 DECLARE @script NVARCHAR(255); 4  5 --缓存所有外键 6 SELECT OBJECT_NAME(fk.constraint_object_id) AS name, OBJECT_NAME(fk.parent_object_id) AS ptab, pc.name AS pcol, OBJECT_NAME(fk.referenced_object_id) AS rtab, rc.name AS rcol 7 INTO #FK_ON 8 FROM sys.foreign_key_columns fk 9 LEFT JOIN sys.columns pc ON fk.parent_column_id = pc.column_id AND fk.parent_object_id = pc.[object_id]10 LEFT JOIN sys.columns rc ON fk.referenced_column_id = rc.column_id AND fk.referenced_object_id = rc.[object_id];11 12 BEGIN TRAN13 14 --删除所有外键15 DECLARE cur_fk_off CURSOR FOR SELECT ‘ALTER TABLE [dbo].[‘ + OBJECT_NAME(parent_object_id) + ‘] DROP CONSTRAINT ‘ + name FROM sys.foreign_keys;16 OPEN cur_fk_off17 FETCH NEXT FROM cur_fk_off INTO @script18 WHILE @@FETCH_STATUS = 019 BEGIN20     EXEC(@script)21     FETCH NEXT FROM cur_fk_off INTO @script22 END23 CLOSE cur_fk_off24 DEALLOCATE cur_fk_off25 26 --清空所有表27 DECLARE cur_tabs CURSOR FOR SELECT ‘TRUNCATE TABLE [dbo].[‘ + name + ‘]‘ FROM sys.tables;28 OPEN cur_tabs29 FETCH NEXT FROM cur_tabs INTO @script30 WHILE @@FETCH_STATUS = 031 BEGIN32     EXEC(@script)33     FETCH NEXT FROM cur_tabs INTO @script34 END35 CLOSE cur_tabs36 DEALLOCATE cur_tabs37 38 --写入初始化数据39 --INSERT INTO ...40 41 --重建所有外键42 DECLARE cur_fk_on CURSOR FOR SELECT ‘ALTER TABLE [dbo].[‘ + ptab + ‘] ADD CONSTRAINT ‘ + name + ‘ FOREIGN KEY ([‘ + pcol + ‘]) REFERENCES [dbo].[‘ + rtab + ‘] ([‘ + rcol + ‘])‘ FROM #FK_ON;43 OPEN cur_fk_on44 FETCH NEXT FROM cur_fk_on INTO @script45 WHILE @@FETCH_STATUS = 046 BEGIN47     EXEC(@script)48     FETCH NEXT FROM cur_fk_on INTO @script49 END50 CLOSE cur_fk_on51 DEALLOCATE cur_fk_on52 53 --清除缓存54 DROP TABLE #FK_ON55 56 COMMIT TRAN57 GO