· 8 years ago · Mar 07, 2018, 01:50 PM
1drop database mytest;
2create database mytest;
3use mytest;
4
5CREATE TABLE mygroup (
6 ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
7) ENGINE=InnoDB;
8
9CREATE TABLE instance (
10 ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
11 GroupID INT NOT NULL,
12 DateTime DATETIME DEFAULT NULL,
13
14 FOREIGN KEY (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,
15 UNIQUE(GroupID)
16) ENGINE=InnoDB;
17
18SET FOREIGN_KEY_CHECKS = 0;
19TRUNCATE table $table_name;
20SET FOREIGN_KEY_CHECKS = 1;
21
22SET FOREIGN_KEY_CHECKS = 0;
23
24TRUNCATE table1;
25TRUNCATE table2;
26
27SET FOREIGN_KEY_CHECKS = 1;
28
29DELETE FROM mytest.instance;
30ALTER TABLE mytest.instance AUTO_INCREMENT = 1;
31
32DELETE FROM `mytable` WHERE `id` > 0
33
34ALTER TABLE <Table Name>
35ADD FOREIGN KEY (<Field Name>) REFERENCES <Foreign Table Name>(<Field Name>);
36
37SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
38SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;`
39SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
40
41DROP TABLE TABLE_NAME;
42TRUNCATE TABLE_NAME;
43
44SET SQL_MODE=@OLD_SQL_MODE;
45SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
46SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
47
48SET NOCOUNT ON
49
50 --DECLARE TOP LEVEL VARIABLE
51
52 DECLARE @FKTable TABLE(
53 fk_id int identity(1,1) primary key,
54 fk_row_id int,
55 fk_name nvarchar(100),
56 fk_parent_table_name nvarchar(100),
57 fk_parent_table_coulumn_name nvarchar(100),
58 fk_reference_table nvarchar(100),
59 fk_reference_table_column_name nvarchar(100)
60 )
61
62 DECLARE @FKTableRef TABLE(
63 fk_id int identity(1,1) primary key,
64 fk_row_id int,
65 fk_name nvarchar(100),
66 fk_parent_table_name nvarchar(100),
67 fk_parent_table_coulumn_name nvarchar(100),
68 fk_reference_table nvarchar(100),
69 fk_reference_table_column_name nvarchar(100)
70 )
71
72 --DROP TEMP TABLE IF EXISTS--------------
73
74 IF Object_Id('TempDB..#FKRefConstructAdd') IS NOT NULL
75 BEGIN
76 DROP TABLE #FKRefConstructAdd
77 END
78
79
80 CREATE TABLE #FKRefConstructAdd
81 (
82 fk_Add_id int primary key identity(1,1),
83 AddScript nvarchar(2000) -- number can be increased
84 )
85
86 DECLARE @MinLoopCount int
87 DECLARE @MaxLoopCount int
88 -------------------------------
89 DECLARE @Error nvarchar(600)
90 --declare @TableName varchar(1000) = '[mrc].[M_LibWKB]' --Test Data
91 ----------------------------
92 DECLARE @InnerMinLoopCount int
93 DECLARE @InnerMaxLoopCount int
94 ------------------------------
95 DECLARE @AddMinLoopCount int
96 DECLARE @AddMaxLoopCount int
97 ---------------------------
98 DECLARE @TransTry varchar(100) ='Try_Transaction' --better name
99
100 --INSERT TABLE FOREIGN KEYS INTO TABLE VARIABLE
101
102 INSERT INTO @FKTable
103 select
104 ROW_NUMBER() OVER (PARTITION BY fk.name ORDER BY fk.name) as fk_row_id,
105 fk.name as fk_name,
106 '['+OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].['+ object_name(fk.parent_object_id)+']' as fk_parent_table_name,
107 '['+c1.name+']' as fk_parent_table_coulumn_name,
108 '[' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + object_name(fk.referenced_object_id) + ']' as fk_reference_table,
109 '['+c2.name+']' as fk_reference_table_column_name
110 from
111 sys.foreign_keys fk
112 inner join
113 sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
114 inner join
115 sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
116 inner join
117 sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id
118 where '['+OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].['+ object_name(fk.parent_object_id)+']' = @TableName
119
120 -------------------INSERT DEPENDECIES-------------------------------------------------
121
122 INSERT INTO @FKTableRef
123 select
124 ROW_NUMBER() OVER (PARTITION BY fk.name ORDER BY fk.name) as fk_row_id,
125 fk.name as fk_name,
126 '['+OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].['+ object_name(fk.parent_object_id)+']' as fk_parent_table_name,
127 '['+c1.name+']' as fk_parent_table_coulumn_name,
128 '[' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + object_name(fk.referenced_object_id) + ']' as fk_reference_table,
129 '['+c2.name+']' as fk_reference_table_column_name
130 from
131 sys.foreign_keys fk
132 inner join
133 sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
134 inner join
135 sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
136 inner join
137 sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id
138 where '['+OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].['+ object_name(fk.referenced_object_id)+']' = @TableName
139
140 SET XACT_ABORT ON -- If transaction fails, rollback operation
141
142 -------------START TRANSACTION--------------------------
143
144 BEGIN TRAN @TransTry
145 BEGIN
146 ---LOOP THROUGH THE RESULT SET
147 SET @MinLoopCount = 1
148 SELECT @MaxLoopCount = (SELECT COUNT(*) FROM @FKTable)
149
150
151 IF @MaxLoopCount > 0
152
153 BEGIN
154 WHILE @MaxLoopCount >= @MinLoopCount
155 BEGIN
156 ---------------------------DECLARE SUPPORT VARABLES---------------------------
157 DECLARE @fkName nvarchar(100)
158 DECLARE @fkParentTableName nvarchar(100)
159 DECLARE @fkParentTableCoulumnName nvarchar(100)
160 DECLARE @fkReferenceTable nvarchar(100)
161 DECLARE @fkReferenceTableColumnName nvarchar(100)
162 DECLARE @sql nvarchar(200)
163
164 -----------------DECLARE SUPPORT INNER VARIABLES---------------------------
165 DECLARE @fkName_Inner nvarchar(100)
166 DECLARE @fkParentTableName_Inner nvarchar(100)
167 DECLARE @fkParentTableCoulumnName_Inner nvarchar(100)
168 DECLARE @fkReferenceTable_Inner nvarchar(100)
169 DECLARE @fkReferenceTableColumnName_Inner nvarchar(100)
170 DECLARE @sql_Inner nvarchar(200)
171
172 ------Set support variables------------
173
174 SET @fkName = (SELECT TOP(1) fk_name FROM @FKTable WHERE fk_id = @MinLoopCount)
175 SET @fkParentTableName = (SELECT TOP(1) fk_parent_table_name FROM @FKTable WHERE fk_id = @MinLoopCount)
176 SET @fkParentTableCoulumnName = (SELECT TOP(1) fk_parent_table_coulumn_name FROM @FKTable WHERE fk_id = @MinLoopCount)
177 SET @fkReferenceTable = (SELECT TOP(1) fk_reference_table FROM @FKTable WHERE fk_row_id = @MinLoopCount)
178 SET @fkReferenceTableColumnName = (SELECT TOP(1) fk_reference_table_column_name FROM @FKTable WHERE fk_id = @MinLoopCount)
179
180 ----------------------------------------Drop Constraint-------------------------------------
181
182 SET @sql = (select 'ALTER TABLE ' + @fkParentTableName + ' DROP CONSTRAINT ' + @fkName)
183 PRINT @sql
184 exec sp_executesql @sql
185
186 ------------------------------Drop Reference Constraints--------------------------------
187 SET @InnerMinLoopCount = 1 --SET THE INNER LOOP
188 SET @InnerMaxLoopCount = (SELECT COUNT(*) FROM @FKTableRef WHERE fk_reference_table = @fkParentTableName)
189 IF @InnerMaxLoopCount > 0
190 BEGIN
191 WHILE @InnerMaxLoopCount >= @InnerMinLoopCount
192 BEGIN
193 ----SET SUPPORTING inner VARIABLES -------------------------
194
195 SET @fkName_Inner = (SELECT TOP(1) fk_name FROM @FKTableRef WHERE fk_id = @InnerMinLoopCount)
196 SET @fkParentTableName_Inner = (SELECT TOP(1) fk_parent_table_name FROM @FKTableRef WHERE fk_id = @InnerMinLoopCount)
197 SET @fkParentTableCoulumnName_Inner = (SELECT TOP(1) fk_parent_table_coulumn_name FROM @FKTableRef WHERE fk_id = @InnerMinLoopCount)
198 SET @fkReferenceTable_Inner = (SELECT TOP(1) fk_reference_table FROM @FKTableRef WHERE fk_row_id = @MinLoopCount)
199 SET @fkReferenceTableColumnName_Inner = (SELECT TOP(1) fk_reference_table_column_name FROM @FKTableRef WHERE fk_id = @InnerMinLoopCount)
200
201
202 SET @sql_Inner = (select 'ALTER TABLE ' + @fkParentTableName_Inner + ' DROP CONSTRAINT ' + @fkName_Inner)
203 print @sql_Inner
204 exec sp_executesql @sql_Inner
205
206 --------------------Add refernced table re-add scripts to array table ----------------------------
207 INSERT INTO #FKRefConstructAdd(AddScript)
208 VALUES('ALTER TABLE ' + @fkParentTableName_Inner + ' WITH NOCHECK ADD CONSTRAINT ' + @fkName_Inner + ' FOREIGN KEY(' + @fkParentTableCoulumnName_Inner+') REFERENCES ' + @fkReferenceTable_Inner + '(' +@fkReferenceTableColumnName_Inner +')')
209
210 INSERT INTO #FKRefConstructAdd(AddScript)
211 VALUES('ALTER TABLE ' + @fkParentTableName_Inner + ' CHECK CONSTRAINT ' + @fkName_Inner)
212
213 ------------------------------------------------------------------
214
215 SET @InnerMinLoopCount = @InnerMinLoopCount + 1
216 PRINT @sql
217 END
218 END
219
220 -----------------------------------------Truncate Table-----------------------------------
221
222 SET @sql = (select 'TRUNCATE TABLE ' + @fkParentTableName)
223 PRINT @sql
224 exec sp_executesql @sql
225
226 ------------------------------------ADD Constraint back------------------------------
227
228 SET @sql = (select 'ALTER TABLE ' + @fkParentTableName + ' WITH NOCHECK ADD CONSTRAINT ' + @fkName + ' FOREIGN KEY(' + @fkParentTableCoulumnName+') REFERENCES ' + @fkReferenceTable + '(' +@fkReferenceTableColumnName +')')
229 PRINT @sql
230 exec sp_executesql @sql
231
232 ------------------------CHECK CONSTRAINT---------------------
233
234 SET @sql = (select 'ALTER TABLE ' + @fkParentTableName + ' CHECK CONSTRAINT ' + @fkName)
235 print @sql
236 exec sp_executesql @sql
237
238 ---------------------------EXECUTE INNER LOOP ADD FK Scripts ---------------------
239
240 SET @AddMinLoopCount = 1
241 SET @AddMaxLoopCount = (select COUNT(*) FROM #FKRefConstructAdd)
242
243 IF @AddMaxLoopCount > 0
244 BEGIN
245 WHILE @AddMaxLoopCount >= @AddMinLoopCount
246 BEGIN
247 SET @sql_Inner = (select top(1) AddScript from #FKRefConstructAdd WHERE fk_Add_id = @AddMinLoopCount)
248 print @sql_Inner
249 exec sp_executesql @sql_Inner
250
251 SET @AddMinLoopCount = @AddMinLoopCount + 1
252
253 END
254 END
255
256 TRUNCATE TABLE #FKRefConstructAdd ---clear the table after use
257
258 SET @MinLoopCount = @MinLoopCount + 1
259 END
260 END
261
262 COMMIT TRAN @TransTry
263 END
264
265 -- SET @Error = ERROR_MESSAGE() --set the error variable
266 --PRINT @@error
267 --PRINT ERROR_MESSAGE()
268 --PRINT ERROR_SEVERITY()
269 --PRINT ERROR_STATE()
270 --PRINT ERROR_LINE()
271
272 SET @Error = ERROR_MESSAGE();
273 SELECT @Error as ERROR--Return the error value