在 SQL Server Management Studio (SSMS) 中執行刪除所有資料時，是不會有任何提醒的!!
例如將 delete * from XX 看成 select * from XX，或者未加篩選條件時，都可以輕易地直接刪除整個資料表的所有資料。
防範類似的悲劇發生，可行做法:

SQL Server 定期備份

當發生誤刪資料時，確保可以進行環原。
異動大量資料前，手動備份一次。

自製警告訊息

SSMS (v17.9) 目前沒有類似提醒的設定，不過可以在 delete trigger 加上類似的檢查，預防不小心刪除整個資料表，若真需要全部刪除前，再手動停用 Trigger，執行刪除後再手動啟Trigger。

建立刪除警告的 Trigger

FOR DELETE

AS

BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;
          
     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats 
         WHERE OBJECT_ID = OBJECT_ID('dbo.MyTable_LOG') 
         AND index_id = 1)
     BEGIN
         RAISERROR('Cannot delete all rows at once.',16,1) 
         ROLLBACK TRANSACTION
         RETURN;
     END
END

停用 Trigger 語法

DISABLE TRIGGER [dbo].[RPTIMG_LOG_delete_trigger] ON [dbo].[RPTIMG_LOG]

啟用 Trigger 語法

ENABLE TRIGGER [dbo].[RPTIMG_LOG_delete_trigger] ON [dbo].[RPTIMG_LOG]

所有資料表，建立預防刪除所有資料的 Trigger

/*--------------------------------------------------
description: 所有資料表，建立預防刪除所有資料的 Trigger 
author: Robin
date: 2018/09/17
testing Code:
--------------------------------------------------
EXEC aDevTool_DBA_Create_Prevent_Delete_All
--------------------------------------------------*/
CREATE Procedure [dev].[aDevTool_DBA_Create_Prevent_Delete_All]
AS
BEGIN

DECLARE @ExampleString1 nvarchar(1024), @ExampleString2 nvarchar(1024)
SET @ExampleString1='
IF EXISTS (SELECT * FROM sys.objects WHERE type = ''TR'' AND name = ''MyTable_delete_all_trigger'')
BEGIN
 DROP TRIGGER dbo.MyTable_delete_all_trigger
END'

SET @ExampleString2='
CREATE TRIGGER dbo.MyTable_delete_all_trigger ON dbo.MyTable
FOR DELETE
AS
BEGIN
 DECLARE @Count int
 SET @Count = @@ROWCOUNT;
         
 IF @Count >= (SELECT SUM(row_count)
  FROM sys.dm_db_partition_stats 
  WHERE OBJECT_ID = OBJECT_ID(''dbo.MyTable'') 
  AND index_id = 1)
 BEGIN
  RAISERROR(''Cannot delete all rows at once.'',16,1) 
  ROLLBACK TRANSACTION
  RETURN;
 END
END';

DECLARE @SchemaName varchar(8), @TableName varchar(32)

DECLARE CURSOR1 CURSOR READ_ONLY LOCAL FOR 
    SELECT SchemaName = t.table_schema,
     TableName = t.table_name
 FROM  information_schema.tables t
 where t.table_type = 'BASE TABLE'
 --and t.TABLE_NAME='ADEVTOOL_SETALLSTOCKONORDER_LOG'
 ORDER BY SchemaName, TableName;

OPEN CURSOR1

FETCH NEXT FROM CURSOR1 INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
 DECLARE @SqlString1 nvarchar(1024), @SqlString2 nvarchar(1024)

 print @SchemaName+'.'+@TableName
    SET @SqlString1 = REPLACE(@ExampleString1, 'dbo.MyTable', @SchemaName+'.'+@TableName)
 SET @SqlString1 = REPLACE(@SqlString1, 'MyTable_', @TableName+'_')
    
 print @SqlString1
 EXECUTE sp_executesql @SqlString1;

 SET @SqlString2 = REPLACE(@ExampleString2, 'dbo.MyTable', @SchemaName+'.'+@TableName)
 SET @SqlString2 = REPLACE(@SqlString2, 'MyTable_', @TableName+'_')
    
 print @SqlString2;
 EXECUTE sp_executesql @SqlString2;

 print 'done'

    FETCH NEXT FROM CURSOR1 INTO @SchemaName, @TableName
END 
CLOSE CURSOR1;
DEALLOCATE CURSOR1;

END

PS: 這段語法查詢是需要授權

GRANT VIEW SERVER STATE TO [AppUser1]; 
GO 
GRANT VIEW ANY DEFINITION TO [AppUser1]; 
GO

參考

Prevent accidental update or delete commands of all rows in a SQL Server table

搜尋此網誌

RobinNotes

Warn for DELETE All records