sql删除数据库中所有表的数据

《sql删除数据库中所有表的数据》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、CREATEPROCEDUREDeleteAllDataASBEGINDECLARE@SQLnvarchar(2000),@CurrentTablesysname,@CurrentSchemasysname--GrabtheserverversionforanystatementswhichneedtobemodifiedbasedupontheserverversionDECLARE@ServerVersionintSET@ServerVersion=(SELECTCAST(LEFT(CAST(SERV

2、ERPROPERTY('ProductVersion')ASvarchar(50)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion')ASvarchar(50)))-1)ASint))--Thissolutionentailsacursor.Alternatively,itcouldbedonewith--theundocumentedstoredproceduresp_msforeachtable,orwithlooplogic.DECLARETab

3、leCursorSCROLLCURSORFORSELECTQUOTENAME(TABLE_SCHEMA)ASschemaname,QUOTENAME(TABLE_NAME)ASnameFROMINFORMATION_SCHEMA.TABLESWHERETABLE_TYPE='BASETABLE'OPENTableCursorFETCHNEXTFROMTableCursorINTO@CurrentSchema,@CurrentTable--DisablealltriggersfirstWHILE@@FETC

4、H_STATUS=0BEGIN--CreateaTSQLstringtodisabletriggersonthecurrenttableSET@SQL=(SELECT'ALTERTABLE'+@CurrentSchema+'.'+@CurrentTable+'DISABLETRIGGERALL')EXECUTEsp_ExecuteSQL@SQL;--Printasuccessorfailuremessage,dependinguponwhetherornotanerrorwasraised.IF@@ERR

5、OR=0BEGINPRINT'Triggerssuccessfullydisabledon'+@CurrentSchema+'.'+@CurrentTableENDELSEBEGINPRINT'Anerrorhasoccuredwhiledisablingtriggerson'+@CurrentSchema+'.'+@CurrentTableEND--CreateaTSQLstringtodisableconstraintsonthecurrenttableSET@SQL=(SELECT'ALTERTAB

6、LE'+@CurrentSchema+'.'+@CurrentTable+'NOCHECKCONSTRAINTALL')EXECUTEsp_ExecuteSQL@SQL;--Printasuccessorfailuremessage,dependinguponwhetherornotanerrorwasraised.IF@@ERROR=0BEGINPRINT'Constraintssuccessfullydisabledon'+@CurrentSchema+'.'+@CurrentTableENDELSE

7、BEGINPRINT'Anerrorhasoccuredwhiledisablingconstraintson'+@CurrentSchema+'.'+@CurrentTableEND--FetchthenexttablefromthecursorFETCHNEXTFROMTableCursorINTO@CurrentSchema,@CurrentTableEND--MovebacktothefirsttableinthecursorFETCHFIRSTFROMTableCursorINTO@Curren

8、tSchema,@CurrentTableWHILE@@FETCH_STATUS=0BEGINIF@ServerVersion>=9--IFwe'reonSQL2005orgreater,wecanuseTry/Catch.BEGINSET@SQL=(SELECT'BEGINTRYTRUNCATETABLE'+@CurrentSchema+'.'+@CurrentTable+'PRINT''Datasuccessfullytr