truncate table 遇到的外键被使用问题解决方案。

在asp.net中操纵数据库，有时候要清空某张或者某些的表，但是这些表使用了外键，而且有的还用到了自增字段，希望清空之后能从1开始。百度谷歌之后小结一下，为以后理解和重复使用保留材料。

---

解决方案一：

1. 清空数据 
2. 有外键也可以, 因为是逆向删除, 从最后一张表删除. 且使用的是delete, 因为truncate不能对有外键的表 
3. 种子问题, 如果表存在种子重设为0, 如不存在就不操作 
4. 加了事务, 中间报错, 有后悔机会 
5. 截断日志功能, 因为使用delete, 删除后日志文件会增大, 可以不使用

 

if( object_id('pr_DataClear') is not null ) drop procedure pr_DataClear go create procedure pr_DataClear as begin transaction declare @cTblName varchar(128) declare cur_Clear cursor for select rtrim(name) from sysobjects where type = 'U' order by crdate desc open cur_Clear declare @cSQL varchar(255) fetch next from cur_Clear into @cTblName while( @@fetch_status = 0) begin set @cSQL = 'delete from ' + @cTblName print @cSQL exec( @cSQL ) if( ident_seed(@cTblName) is not null ) begin dbcc checkident( @cTblName, reseed, 0 ) print '有种子且成功重置为1' end fetch next from cur_Clear into @cTblName end close cur_Clear deallocate cur_Clear commit go -- 清空所有表数据 exec pr_DataClear -- 截断日志 backup log LZ的数据库 with no_log dbcc shrinkdatabase( LZ的数据库 ) dbcc updateusage( LZ的数据库 ) -- 查看表空间(概数) select object_name(id) as 表名, (rtrim(8*reserved/1024) + 'MB') as 总量, (rtrim(8*dpages/1024) + 'MB') as 已使用, (rtrim(8*(reserved-dpages)/1024) + 'MB') as 未使用, (rtrim(8*dpages/1024-rows/1024*minlen/1024) + 'MB' ) as 空隙 from sysindexes where indid=1 order by reserved desc 

以上代码只要在select rtrim(name) from sysobjects where type = 'U' order by crdate desc中添加过滤条件即可过滤一些表，我是改成，where type = 'U' and rtrim(name) in('IPControl','Student','Teacher_Course','Score','Paper','PaperDetail') order by crdate desc 这样使得in里面的表被清空，而数据库中其他表内容被保留下来。

 

==============================

在ASP.net中要直接使用这段代码会出问题，我猜想主要原因在于go这个词上。

 

解决方法就是将这一大片的代码分为若干个sql语句，然后顺次执行。另外，个人觉得也无需将所有的语句段都复制使用，选择自己需要的就可以了。

 

=============================

 

解决方案二：

 

---

 

declare @name varchar(200),@tmp1 varchar(500),@tmp2 varchar(500) create table #tmp ( string varchar(8000) ) SELECT 表名称=object_name(b.fkeyid) ,外键名称=a.name ,引用的列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) ,引用的表名=object_name(b.rkeyid) ,已引用的列名=(SELECT name FROM syscolumns WHERE colid=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.id where 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_test FETCH NEXT FROM cur_test INTO @name WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN select @tmp1='',@tmp2='' select @tmp1=@tmp1+'['+引用的列名+'],',@tmp2=@tmp2+'['+已引用的列名+'],' from #t where 外键名称=@name insert into #tmp select top 1 'ALTER TABLE [DBO].['+表名称+'] ADD CONSTRAINT ['+@name+'] FOREIGN KEY ( '+left(@tmp1,len(@tmp1)-1)+' ) REFERENCES ['+引用的表名+'] ( '+left(@tmp2,len(@tmp2)-1)+' )' from #t where 外键名称=@name END FETCH NEXT FROM cur_test INTO @name END CLOSE cur_test DEALLOCATE cur_test drop table #t DECLARE @STRING VARCHAR(8000) WHILE EXISTS(SELECT NAME FROM SYSOBJECTS WHERE TYPE='F') BEGIN SELECT @STRING='ALTER TABLE '+<mce:script type="text/javascript" src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/themes/advanced/langs/zh.js"></mce:script><mce:script type="text/javascript" src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js"></mce:script>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'ISUSERTABLE') = 1) B WHERE A.PARENT_OBJ=B.ID EXEC(@STRING) END exec sp_msforeachtable @command1= 'truncate table ?', @whereand=' and name not in(''Users''<mce:script type="text/javascript" src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/themes/advanced/langs/zh.js"></mce:script><mce:script type="text/javascript" src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js"></mce:script>)' declare cur_test2 cursor for select string from #tmp open cur_test2 FETCH NEXT FROM cur_test2 INTO @string WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN exec(@string) PRINT @STRING END FETCH NEXT FROM cur_test2 INTO @string END CLOSE cur_test2 DEALLOCATE cur_test2 drop table #tmp  

 

 

都看不太懂，但是都挺有用的。以后如果有进步，再回头来好好理解。这当中也有一个对表进行过滤的地方，@command1= 'truncate table ?', @whereand=' and name not in(''Users'')'  我就设置了一个name not in(''Users'')'  ，结果运行之后只有Users表被保留下来，其他全部被清空。