CREATE PROCEDURE DeleteAllDataASBEGINDECLARE @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 intSET @ServerVersion = (SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS varchar(50)), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS varchar(50))) - 1) A S 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 FORSELECT QUOTENAME(TA BLE_SCHEMA) AS schemaname, QUOTENAME(TA BLE_NAME) AS name FROM INFORMATION_SCHEMA.TABLES W HERE TA BLE_TYPE = 'BASE TA BLE' OPEN TableCursorFETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable--Disable all triggers firstWHILE @@FETCH_STATUS = 0BEGIN--Create a TSQL string to disable triggers on the current tableSET @SQL =(SELECT 'ALTER TABLE '+ @CurrentSchema + '.' + @CurrentTable + ' DISABLE TRIGGER A LL')EXECUTE sp_ExecuteSQL @SQL;--Print a success or failure message, depending upon whether or not an error was raised.IF @@ERROR = 0BEGINPRINT 'Triggers successfully disabled on ' + @CurrentSchema + '.' +@CurrentTableENDELSEBEGINPRINT 'An error has occured while disabling triggers on ' + @CurrentSchema + '.' + @CurrentTableEND--Create a TSQL string to disable constraints on the current tableSET @SQL =(SELECT 'ALTER TABLE '+ @CurrentSchema + '.' + @CurrentTable + ' NOCHECK CONSTRAINT A LL')EXECUTE sp_ExecuteSQL @SQL;--Print a success or failure message, depending upon whether or not an error was raised.IF @@ERROR = 0BEGINPRINT 'Constraints successfully disabled on ' + @CurrentSchema + '.' +@CurrentTableENDELSEBEGINPRINT 'An error has occured while disabling constraints on ' +@CurrentSchema + '.' + @CurrentTableEND--Fetch the next table from the cursorFETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTableEND--Move back to the first table in the cursorFETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTableWHILE @@FETCH_STATUS = 0BEGINIF @ServerVersion >= 9 --IF we're on SQL 2005 or greater, we can use Try/Catch.BEGINSET @SQL = (SELECT 'BEGIN TRYTRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + 'PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + '''END TRYBEGIN CATCHDELETE FROM ' + @CurrentSchema + '.' + @Cu rrentTable + 'IF EXISTS(SELECT ''A'' FROM information_schema.columnsWHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' +@CurrentTable + '''),column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + '''AND QUOTENAME(TA BLE_NAME) = ''' + @CurrentTable + ''')BEGINDBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0)ENDPRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + '''END CATCH')ENDELSE --We're on SQL 2000, so we need to check for foreign key existence first.BEGINSET @SQL = (SELECT 'IF OBJECTPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), ''TableHasForeignRef'') <> 1BEGINTRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + 'PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' +@CurrentTable + '''ENDELSEBEGINDELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + 'IF EXISTS(SELECT ''A'' FROM information_schema.columnsWHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''),column_name,''IsIdentity'')=1 ANDQUOTENAME(TA BLE_SCHEMA) = ''' + @CurrentSchema + '''AND QUOTENAME(TA BLE_NAME) = ''' + @CurrentTable + ''')BEGINDBCC CHECKIDENT(''' + @CurrentSchema + '.' +@CurrentTable + ''', RESEED, 0)ENDPRINT ''Data successfully deleted from ' + @CurrentSchema + '.' +@CurrentTable + '''END')ENDEXECUTE sp_ExecuteSQL @SQL;--Fetch the next table from the cursorFETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTableEND--Move back to the first table in the cursorFETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTableWHILE @@FETCH_STATUS = 0BEGIN--Reenable triggersSET @SQL = (SELECT 'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' ENABLE TRIGGER A LL')EXECUTE sp_ExecuteSQL @SQL;--Print a success or failure message, depending upon whether or not an error was raised.IF @@ERROR = 0BEGINPRINT 'Triggers successfully reenabled on ' + @CurrentSchema + '.' +@CurrentTableENDELSEBEGINPRINT 'An error has occured while reenabling triggers on ' + @CurrentSchema + '.' + @CurrentTableEND--Now reenable constraintsSET @SQL = (SELECT 'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' CHECK CONSTRAINT A LL')EXECUTE sp_ExecuteSQL @SQL;--Print a success or failure message, depending upon whether or not an error was raised.IF @@ERROR = 0BEGINPRINT 'Constraints successfully disabled on ' + @CurrentTableENDELSEBEGINPRINT 'An error has occured while disabling constraints on ' + @CurrentTable END--Fetch the next table from the cursorFETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTableEND--CLOSE and DEA LLOCATE our cursor CLOSE TableCursorDEA LLOCATE TableCursorEND--EXEC DeleteAllData。