2024年4月19日发(作者：)
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_ 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_s
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_s
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
发布评论