sql清空所有数据库表记录的存储过程.sql

/*** 关于： MS SQL Server 2000 存储过程 - 2* 作者：彭建军* 存储过程名称:  TruncateAllTables* 存储过程功能： 清空数据库中除 ESCAPE 表中的记录除外的所有表中的数据！* 测试环境：SQL Server 2000 SP4 + Windows XP Sp2* 更新时间：2006-7-8 16:36**/USE DBnameGOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'P' AND NAME = 'TruncateAllTables') DROP PROCEDURE TruncateAllTablesGO--该存储过程功能:CREATE PROCEDURE TruncateAllTablesWITH ENCRYPTIONASBEGIN TRANSACTION IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'EscapeTable')  DROP TABLE EscapeTable CREATE TABLE EscapeTable (  Name VARCHAR(100) ) --这里非常重要，请将不想被清空数据的表名插入 EscapeTable 表中 INSERT INTO EscapeTable  SELECT 'T_Sta_TableIdentity'UNION ALL  SELECT 'T2' DECLARE @SQLString NVARCHAR(500) DECLARE @TableName VARCHAR(100) DECLARE MyCursor CURSOR  FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME NOT IN(SELECT NAME FROM EscapeTable)  ORDER BY ID OPEN MyCurSor  FETCH NEXT FROM MyCurSor INTO @TableName WHILE (@@FETCH_STATUS = 0) BEGIN  SET @SQLString = 'TRUNCATE TABLE ' + @TableName  EXECUTE sp_executesql @SQLString    FETCH NEXT FROM MyCurSor INTO @TableName ENDCLOSE MyCursorDEALLOCATE MyCursorDROP TABLE EscapeTablePRINT 'Procedure is successful done.'SELECT 1COMMIT TRANSACTIONGO--重要！执行该存储过程将清空不包含在 ESCAPE 表中的记录内的所有表中的数据！执行前请确认！INSERT INTO T1 SELECT 'abc' UNION ALL SELECT 'opq'INSERT INTO T2 SELECT 'abc' UNION ALL SELECT 'opq'EXECUTE TruncateAllTablesGOSELECT * FROM T1SELECT * FROM T2GO