Original content: How to delete all the data in the database, then reset all automatic increment? Too much, unable to be done manually. Http://community.9cbs.net/expert/topic/3094/3094555.xml?temp=.2920954
/ * - Originally planned to disable all foreign key constraints Exec sp_msforeachtable "ALTER TABLE? NOCHECK CONSTRAINT ALL" - then delete Data EXEC SP_MSFOREACHTABLE "TRUNCATE TABLE?" - Enable all foreign key constraints Exec sp_msforeachtable "Alter Table" ALTER TABLE CHECK CONSTRAINT ALL "- But after the disabled, Truncate Table will not prompt the conflict * /
- Now my thoughts are (the statement is optimized):
- The first part, the statement that generates a foreign key is saved to #tmpdeclare @name varchar (200), @Tmp1 varchar (500), @ Tmp2 Varchar (500)
Create Table #tmp (String Varchar (8000))
SELECT table name = Object_name (b.fKeyID), foreign key name = a.name, reference to column name = (select name from syscolumns where color = B.FKey and ID = B.FKEYID), referenced table name = Object_name B.RKeyID, referenced column name = (Select Name from syscolumns where color = b.rkey and id = b.rkeyid) INTO #T from sysobjects a join sysforeignkeys b on A.Id = B.Constid Join Sysobjects c on A.Parent_obj = C.Idwhere a.xtype = 'f' and c.xtype = 'u'
declare cur_test cursor for select a.name from sysobjects a join sysobjects c on a.parent_obj = c.id where a.xtype = 'f' and c.xtype = 'U'open cur_testFETCH NEXT FROM cur_test INTO @nameWHILE (@@ fetch_status <> -1) begin if (@@ fetch_status <> -2) begin select @ TMP1 = '', @ TMP2 = '' SELECT @ TMP1 = @ TMP1 '[' reference column name '],', @ TMP2 = @ TMP2 '[' The column name ']],' from #t Where foreign key name = @ name insert into #tmp Select Top 1 'Alter Table [DBO]. [' Table Name '] Add constraint [' @ Name '] Foreign Key (' LEFT (@ TMP1, LEN (@ TMP1) -1) ') References [' reference table name '] (' Left (@ TMP2, LEN (@ TMP2) -1) ')' from #t where foreign key name = @ name end fetch from cur_test @nameendclose cur_testdeallocate cur_testdrop table #t
- Part 2, delete all foreign keys declare @String varchar (8000) while exists (select name from sysobjects where type = 'f') begin select @ string = 'alter table' b.Name 'Drop Constraint' A .Name char (13) from (select parent_obj, name from sysobjects where type = 'f') a, (select ID, name from sysobjects where objectproperty (id, n'susertable ') = 1) b Where a.parent_obj = B.ID EXEC (@string) end
- Part III, delete the record of all tables, and put the Identity Reset EXEC SP_MSFOREACHTABLE "TRUNCATE TABLE?"
- Part 4, executing the statement of the outer key inside #TMP, recover foreign key declare cur_test2 Cursor for select string from #TMP
open cur_test2FETCH NEXT FROM cur_test2 INTO @stringWHILE (@@ fetch_status <> -1) BEGIN IF (@@ fetch_status <> -2) BEGIN exec (@string) PRINT @STRING END FETCH NEXT FROM cur_test2 INTO @stringENDCLOSE cur_test2DEALLOCATE cur_test2
DROP TABLE #TMP
