原始连接:http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PurgeAllTables&referringTitle=Home
Deleting All Data From All User Tables In A Database
Aaron Alton - May 2, 2008
For various reasons, one may need to delete all rows from all tables in a SQL Server database. If the number of tables in a database is great, this task can become quite tedious if performed manually. Additionally, triggers and constraints which enforce referential integrity must be considered if DELETE or TRUNCATE statements are to succeed. The solution below provides a stored procedure (working with with SQL 2000/2005/2008) which, when run, will delete ALL data from ALL tables in the current database. Please use with appropriate caution.
CREATE PROCEDURE DeleteAllData AS BEGIN DECLARE @SQL nvarchar(2000), @CurrentTable sysname, @CurrentSchema sysname --Grab the server version for any statements which need to be modified based upon the server version DECLARE @ServerVersion int SET @ServerVersion = (SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS varchar(50)), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS varchar(50))) - 1) AS int)) --This solution entails a cursor. Alternatively, it could be done with --the undocumented stored procedure sp_msforeachtable, or with loop logic. DECLARE TableCursor SCROLL CURSOR FOR SELECT QUOTENAME(TABLE_SCHEMA) AS schemaname, QUOTENAME(TABLE_NAME) AS name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable --Disable all triggers first WHILE @@FETCH_STATUS = 0 BEGIN --Create a TSQL string to disable triggers on the current table SET @SQL = (SELECT 'ALTER TABLE '+ @CurrentSchema + '.' + @CurrentTable + ' DISABLE TRIGGER ALL') EXECUTE sp_ExecuteSQL @SQL; --Print a success or failure message, depending upon whether or not an error was raised. IF @@ERROR = 0 BEGIN PRINT 'Triggers successfully disabled on ' + @CurrentSchema + '.' + @CurrentTable END ELSE BEGIN PRINT 'An error has occured while disabling triggers on ' + @CurrentSchema + '.' + @CurrentTable END --Create a TSQL string to disable constraints on the current table SET @SQL = (SELECT 'ALTER TABLE '+ @CurrentSchema + '.' + @CurrentTable + ' NOCHECK CONSTRAINT ALL') EXECUTE sp_ExecuteSQL @SQL; --Print a success or failure message, depending upon whether or not an error was raised. IF @@ERROR = 0 BEGIN PRINT 'Constraints successfully disabled on ' + @CurrentSchema + '.' + @CurrentTable END ELSE BEGIN PRINT 'An error has occured while disabling constraints on ' + @CurrentSchema + '.' + @CurrentTable END --Fetch the next table from the cursor FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable END --Move back to the first table in the cursor FETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTable WHILE @@FETCH_STATUS = 0 BEGIN IF @ServerVersion >= 9 --IF we're on SQL 2005 or greater, we can use Try/Catch. BEGIN SET @SQL = (SELECT 'BEGIN TRY TRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + ''' END TRY BEGIN CATCH DELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + ' IF EXISTS(SELECT ''A'' FROM information_schema.columns WHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + ''' AND QUOTENAME(TABLE_NAME) = ''' + @CurrentTable + ''') BEGIN DBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0) END PRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + ''' END CATCH') END ELSE --We're on SQL 2000, so we need to check for foreign key existence first. BEGIN SET @SQL = (SELECT 'IF OBJECTPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), ''TableHasForeignRef'') <> 1 BEGIN TRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + ''' END ELSE BEGIN DELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + ' IF EXISTS(SELECT ''A'' FROM information_schema.columns WHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + ''' AND QUOTENAME(TABLE_NAME) = ''' + @CurrentTable + ''') BEGIN DBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0) END PRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + ''' END') END EXECUTE sp_ExecuteSQL @SQL; --Fetch the next table from the cursor FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable END --Move back to the first table in the cursor FETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTable WHILE @@FETCH_STATUS = 0 BEGIN --Reenable triggers SET @SQL = (SELECT 'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' ENABLE TRIGGER ALL') EXECUTE sp_ExecuteSQL @SQL; --Print a success or failure message, depending upon whether or not an error was raised. IF @@ERROR = 0 BEGIN PRINT 'Triggers successfully reenabled on ' + @CurrentSchema + '.' + @CurrentTable END ELSE BEGIN PRINT 'An error has occured while reenabling triggers on ' + @CurrentSchema + '.' + @CurrentTable END --Now reenable constraints SET @SQL = (SELECT 'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' CHECK CONSTRAINT ALL') EXECUTE sp_ExecuteSQL @SQL; --Print a success or failure message, depending upon whether or not an error was raised. IF @@ERROR = 0 BEGIN PRINT 'Constraints successfully disabled on ' + @CurrentTable END ELSE BEGIN PRINT 'An error has occured while disabling constraints on ' + @CurrentTable END --Fetch the next table from the cursor FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable END --CLOSE and DEALLOCATE our cursor CLOSE TableCursor DEALLOCATE TableCursor END --EXEC DeleteAllData