<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
原帖内容:
怎样把数据库中所有数据删除,然后把所有的自动增量复位?
表太多,无法手工完成。
http://community.csdn.net/Expert/topic/3094/3094555.xml?temp=.2920954
/*
--原本打算这样
--先禁用所有外键约束
execsp_msforeachtable"altertable?nocheckCONSTRAINTall"
--然后删除数据
execsp_msforeachtable"truncatetable?"
--再启用所有外键约束
execsp_msforeachtable"altertable?checkconstraintall"
--但是禁用了以后,truncatetable不行,会提示冲突
*/
--现在我的想法是(语句待优化):
--第一部分,生成建立外键的语句保存到#tmp
declare@namevarchar(200),@tmp1varchar(500),@tmp2varchar(500)
createtable#tmp
(
stringvarchar(8000)
)
SELECT 表名称=object_name(b.fkeyid)
,外键名称=a.name
,引用的列名=(SELECTnameFROMsyscolumnsWHEREcolid=b.fkeyANDid=b.fkeyid)
,引用的表名=object_name(b.rkeyid)
,已引用的列名=(SELECTnameFROMsyscolumnsWHEREcolid=b.rkeyANDid=b.rkeyid)
into#tFROMsysobjectsa
joinsysforeignkeysbona.id=b.constid
joinsysobjectscona.parent_obj=c.id
wherea.xtype='f'ANDc.xtype='U'
declarecur_testcursorfor
selecta.namefromsysobjectsajoinsysobjectscona.parent_obj=c.idwherea.xtype='f'andc.xtype='U'
opencur_test
FETCHNEXTFROMcur_testINTO@name
WHILE(@@fetch_status<>-1)
BEGIN
IF(@@fetch_status<>-2)
BEGIN
select@tmp1='',@tmp2=''
select@tmp1=@tmp1+'['+引用的列名+'],',@tmp2=@tmp2+'['+已引用的列名+'],'from#twhere外键名称=@name
insertinto#tmpselecttop1'ALTERTABLE[DBO].['+表名称+']ADDCONSTRAINT['+@name+']FOREIGNKEY('+left(@tmp1,len(@tmp1)-1)+')REFERENCES['+引用的表名+']('+left(@tmp2,len(@tmp2)-1)+')'from#twhere外键名称=@name
END
FETCHNEXTFROMcur_testINTO@name
END
CLOSEcur_test
DEALLOCATEcur_test
droptable#t
--第二部分,删除所有外键
DECLARE@STRINGVARCHAR(8000)
WHILEEXISTS(SELECTNAMEFROMSYSOBJECTSWHERETYPE='F')
BEGIN
SELECT@STRING='ALTERTABLE'+B.NAME+'DROPCONSTRAINT'+A.NAME+CHAR(13)
FROM(SELECTPARENT_OBJ,NAMEFROMSYSOBJECTSWHERETYPE='F')A,
(SELECTID,NAMEFROMSYSOBJECTSWHEREOBJECTPROPERTY(ID,N'ISUSERTABLE')=1)B
WHEREA.PARENT_OBJ=B.ID
EXEC(@STRING)
END
--第三部分,删除所有表的记录,并且把identity复位
execsp_msforeachtable"truncatetable?"
--第4部分,执行#tmp里面的建立外键的语句,恢复外键
declarecur_test2cursorforselectstringfrom#tmp
opencur_test2
FETCHNEXTFROMcur_test2INTO@string
WHILE(@@fetch_status<>-1)
BEGIN
IF(@@fetch_status<>-2)
BEGIN
exec(@string)
PRINT@STRING
END
FETCHNEXTFROMcur_test2INTO@string
END
CLOSEcur_test2
DEALLOCATEcur_test2
droptable#tmp
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
原帖内容:
怎样把数据库中所有数据删除,然后把所有的自动增量复位?
表太多,无法手工完成。
http://community.csdn.net/Expert/topic/3094/3094555.xml?temp=.2920954
/*
--原本打算这样
--先禁用所有外键约束
execsp_msforeachtable"altertable?nocheckCONSTRAINTall"
--然后删除数据
execsp_msforeachtable"truncatetable?"
--再启用所有外键约束
execsp_msforeachtable"altertable?checkconstraintall"
--但是禁用了以后,truncatetable不行,会提示冲突
*/
--现在我的想法是(语句待优化):
--第一部分,生成建立外键的语句保存到#tmp
declare@namevarchar(200),@tmp1varchar(500),@tmp2varchar(500)
createtable#tmp
(
stringvarchar(8000)
)
SELECT 表名称=object_name(b.fkeyid)
,外键名称=a.name
,引用的列名=(SELECTnameFROMsyscolumnsWHEREcolid=b.fkeyANDid=b.fkeyid)
,引用的表名=object_name(b.rkeyid)
,已引用的列名=(SELECTnameFROMsyscolumnsWHEREcolid=b.rkeyANDid=b.rkeyid)
into#tFROMsysobjectsa
joinsysforeignkeysbona.id=b.constid
joinsysobjectscona.parent_obj=c.id
wherea.xtype='f'ANDc.xtype='U'
declarecur_testcursorfor
selecta.namefromsysobjectsajoinsysobjectscona.parent_obj=c.idwherea.xtype='f'andc.xtype='U'
opencur_test
FETCHNEXTFROMcur_testINTO@name
WHILE(@@fetch_status<>-1)
BEGIN
IF(@@fetch_status<>-2)
BEGIN
select@tmp1='',@tmp2=''
select@tmp1=@tmp1+'['+引用的列名+'],',@tmp2=@tmp2+'['+已引用的列名+'],'from#twhere外键名称=@name
insertinto#tmpselecttop1'ALTERTABLE[DBO].['+表名称+']ADDCONSTRAINT['+@name+']FOREIGNKEY('+left(@tmp1,len(@tmp1)-1)+')REFERENCES['+引用的表名+']('+left(@tmp2,len(@tmp2)-1)+')'from#twhere外键名称=@name
END
FETCHNEXTFROMcur_testINTO@name
END
CLOSEcur_test
DEALLOCATEcur_test
droptable#t
--第二部分,删除所有外键
DECLARE@STRINGVARCHAR(8000)
WHILEEXISTS(SELECTNAMEFROMSYSOBJECTSWHERETYPE='F')
BEGIN
SELECT@STRING='ALTERTABLE'+B.NAME+'DROPCONSTRAINT'+A.NAME+CHAR(13)
FROM(SELECTPARENT_OBJ,NAMEFROMSYSOBJECTSWHERETYPE='F')A,
(SELECTID,NAMEFROMSYSOBJECTSWHEREOBJECTPROPERTY(ID,N'ISUSERTABLE')=1)B
WHEREA.PARENT_OBJ=B.ID
EXEC(@STRING)
END
--第三部分,删除所有表的记录,并且把identity复位
execsp_msforeachtable"truncatetable?"
--第4部分,执行#tmp里面的建立外键的语句,恢复外键
declarecur_test2cursorforselectstringfrom#tmp
opencur_test2
FETCHNEXTFROMcur_test2INTO@string
WHILE(@@fetch_status<>-1)
BEGIN
IF(@@fetch_status<>-2)
BEGIN
exec(@string)
PRINT@STRING
END
FETCHNEXTFROMcur_test2INTO@string
END
CLOSEcur_test2
DEALLOCATEcur_test2
droptable#tmp
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>