/* =====================================================================
清空数据库内所有用户表数据 —— 仅保留表结构
---------------------------------------------------------------------
环境 : Microsoft SQL Server 2008 及以上（脚本只用 2005/2008 兼容语法，
未使用 IIF / CONCAT / THROW / STRING_AGG / OFFSET-FETCH 等新语法）
生成 : 2026-10-27
风险 : ★★★★★ 不可逆。执行后数据无法通过任何手段恢复。
---------------------------------------------------------------------
【执行前置条件 —— 逐条确认，缺一条都不要跑】
1. 已做完整备份（FULL + 可选 LOG），并确认备份文件可还原。
2. 该库没有事务复制 / 合并复制 / 快照复制在运行（有则先停分发代理）。
3. 该库没有开启 CDC 变更数据捕获（否则 DELETE 会往 cdc.* 表写日志）。
4. 没有长事务或其他会话占用该库（可在维护窗口执行）。
5. 连接账号需要有 db_owner 或等同权限（ALTER TABLE / DBCC 需要）。
---------------------------------------------------------------------
【脚本做了什么】
- 遍历当前库全部用户表（排除 is_ms_shipped 系统表、dtproperties、sysdiagrams）
- 记录并禁用：所有外键约束、CHECK 约束、表上的 DML 触发器
- 逐表清空：
默认 -> DELETE FROM（最安全，兼容索引视图/FILESTREAM）
@UseTruncate = 1 -> 未被任何外键引用的表优先 TRUNCATE（更快且自动重置种子），
失败则自动回退 DELETE
- 逐表独立事务执行，单表失败不中断整体，最后汇总失败清单
- 重置 IDENTITY 种子为 1（仅对用 DELETE 清空的表；TRUNCATE 自带重置）
- 恢复触发器、恢复约束（统一 WITH CHECK 重新验证；表已空，验证零成本）
- 不删除表、索引、约束定义、视图、存储过程、函数、用户
- 不收缩数据库文件（需要的话执行后单独 DBCC SHRINKFILE）
---------------------------------------------------------------------
【万一脚本中途被中断】
约束/触发器会停留在禁用状态，手工补恢复即可：
-- 生成恢复语句
SELECT N'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.'
+ QUOTENAME(t.name) + N' WITH CHECK CHECK CONSTRAINT ALL;'
FROM sys.tables t WHERE t.is_ms_shipped = 0
-- 触发器同理：ENABLE TRIGGER 名称 ON 表名
直接重跑本脚本也可以（重复执行是幂等的，已空的表再删一次无害）。
---------------------------------------------------------------------
【编码提醒】
本文件是 UTF-8。用 sqlcmd 执行必须加 -f 65001，否则中文 Windows
默认按 GBK 解码会产生字节漂移、破坏语句：
sqlcmd -S . -d DB_SH01 -E -i "清空所有表数据_保留表结构.sql" -f 65001
SSMS 直接打开执行则无需处理。
===================================================================== */
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET XACT_ABORT OFF; -- 逐表独立事务，单表报错不影响后续表
GO
USE [DB_SH01]; -- ★★★ 改成你要清空的库名 ★★★
GO
PRINT '============================================================';
PRINT ' 目标数据库：' + DB_NAME();
PRINT ' 服务器 ：' + @@SERVERNAME;
PRINT ' 执行时间 ：' + CONVERT(varchar(19), GETDATE(), 120);
PRINT '============================================================';
GO
/* =====================================================================
第 1 步（只读）：预览将被清空的表清单与行数
—— 建议先单独执行这一段，核对清单无误再继续往下
===================================================================== */
SELECT s.name AS 架构,
t.name AS 表名,
SUM(ISNULL(p.rows, 0)) AS 约行数,
(SELECT COUNT(*) FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = t.object_id) AS 被几个外键引用,
(SELECT COUNT(*) FROM sys.foreign_keys fk
WHERE fk.parent_object_id = t.object_id) AS 自身外键数,
CASE t.is_replicated WHEN 1 THEN '是(注意复制)' ELSE '' END AS 是否复制表
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
WHERE t.is_ms_shipped = 0
AND t.name NOT IN (N'dtproperties', N'sysdiagrams')
GROUP BY s.name, t.name, t.object_id, t.is_replicated
ORDER BY s.name, t.name;
SELECT COUNT(*) AS 待清空表总数,
SUM(ISNULL(p.rows, 0)) AS 待清空数据总量_约
FROM sys.tables t
LEFT JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
WHERE t.is_ms_shipped = 0
AND t.name NOT IN (N'dtproperties', N'sysdiagrams');
GO
/* =====================================================================
第 2 步（写操作）：清空全部表数据
★ @Confirm 必须手动改成 1，否则脚本直接中止，防止手滑
===================================================================== */
SET NOCOUNT ON;
DECLARE @Confirm BIT = 0; -- ★★★ 改成 1 才会真正执行 ★★★
DECLARE @UseTruncate BIT = 0; -- 1 = 无外键引用的表用 TRUNCATE 提速；0 = 全部 DELETE
IF @Confirm = 0
BEGIN
PRINT '>>> 已中止：@Confirm 仍未置为 1。本脚本不会删除任何数据。';
RETURN;
END
/* ---------- 2.1 生成待处理表清单 ---------- */
IF OBJECT_ID('tempdb..#tabs') IS NOT NULL DROP TABLE #tabs;
CREATE TABLE #tabs
(
id INT IDENTITY(1, 1) PRIMARY KEY,
schema_name SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
quoted NVARCHAR(300) NOT NULL, -- 形如 [dbo].[Table]
fk_ref INT NOT NULL, -- 被多少外键引用
row_count BIGINT NULL,
action VARCHAR(10) NOT NULL,
status NVARCHAR(1000) NULL
);
INSERT INTO #tabs (schema_name, table_name, quoted, fk_ref, row_count, action)
SELECT s.name,
t.name,
QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
(SELECT COUNT(*) FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = t.object_id),
ISNULL((SELECT SUM(p.rows) FROM sys.partitions p
WHERE p.object_id = t.object_id AND p.index_id IN (0, 1)), 0),
CASE WHEN @UseTruncate = 1
AND NOT EXISTS (SELECT 1 FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = t.object_id)
AND t.is_replicated = 0
THEN 'TRUNCATE' ELSE 'DELETE'
END
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0
AND t.name NOT IN (N'dtproperties', N'sysdiagrams');
DECLARE @total INT, @i INT, @q NVARCHAR(300), @act VARCHAR(10), @stmt NVARCHAR(MAX);
SELECT @total = COUNT(*) FROM #tabs;
IF @total = 0
BEGIN
PRINT '>>> 未发现可清空的用户表，脚本结束。';
RETURN;
END
PRINT '>>> 共发现 ' + CONVERT(varchar(10), @total) + ' 张用户表，开始处理……';
/* ---------- 2.2 记录并禁用所有 DML 触发器（防止清空动作触发业务审计逻辑） ---------- */
IF OBJECT_ID('tempdb..#trg') IS NOT NULL DROP TABLE #trg;
CREATE TABLE #trg
(
id INT IDENTITY(1, 1) PRIMARY KEY,
quoted NVARCHAR(300) NOT NULL,
trg_name SYSNAME NOT NULL,
was_disabled BIT NOT NULL
);
INSERT INTO #trg (quoted, trg_name, was_disabled)
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name),
tr.name,
tr.is_disabled
FROM sys.triggers tr
JOIN sys.tables t ON t.object_id = tr.parent_id
WHERE t.is_ms_shipped = 0
AND t.name NOT IN (N'dtproperties', N'sysdiagrams')
AND tr.parent_class = 1; -- 1 = 表上的 DML 触发器
SET @stmt = N'';
SELECT @stmt = @stmt + N'DISABLE TRIGGER ' + QUOTENAME(trg_name)
+ N' ON ' + quoted + N';' + CHAR(10)
FROM #trg
WHERE was_disabled = 0;
IF LEN(@stmt) > 0 EXEC sp_executesql @stmt;
DECLARE @trgCnt INT;
SELECT @trgCnt = COUNT(*) FROM #trg WHERE was_disabled = 0;
PRINT '>>> 已临时禁用 DML 触发器 ' + CONVERT(varchar(10), @trgCnt) + ' 个（原本已禁用的保持原状）。';
/* ---------- 2.3 禁用所有外键与 CHECK 约束（允许任意顺序清空） ---------- */
SET @stmt = N'';
SELECT @stmt = @stmt + N'ALTER TABLE ' + quoted + N' NOCHECK CONSTRAINT ALL;' + CHAR(10)
FROM #tabs;
EXEC sp_executesql @stmt;
PRINT '>>> 已禁用全部外键与 CHECK 约束。';
/* ---------- 2.4 逐表清空（逐表独立事务，失败不中断） ---------- */
SET @i = 1;
WHILE @i <= @total
BEGIN
SELECT @q = quoted, @act = action FROM #tabs WHERE id = @i;
IF @act = 'TRUNCATE'
BEGIN
BEGIN TRY
SET @stmt = N'TRUNCATE TABLE ' + @q + N';';
EXEC sp_executesql @stmt;
UPDATE #tabs SET status = N'OK(TRUNCATE)' WHERE id = @i;
END TRY
BEGIN CATCH
-- TRUNCATE 受限（如被索引视图引用）时回退为 DELETE
BEGIN TRY
SET @stmt = N'DELETE FROM ' + @q + N';';
EXEC sp_executesql @stmt;
UPDATE #tabs SET status = N'OK(DELETE 回退)' WHERE id = @i;
END TRY
BEGIN CATCH
UPDATE #tabs SET status = N'FAIL: ' + ERROR_MESSAGE() WHERE id = @i;
END CATCH
END CATCH
END
ELSE
BEGIN
BEGIN TRY
SET @stmt = N'DELETE FROM ' + @q + N';';
EXEC sp_executesql @stmt;
UPDATE #tabs SET status = N'OK(DELETE)' WHERE id = @i;
END TRY
BEGIN CATCH
UPDATE #tabs SET status = N'FAIL: ' + ERROR_MESSAGE() WHERE id = @i;
END CATCH
END
SET @i = @i + 1;
END
PRINT '>>> 数据清空阶段完成。';
/* ---------- 2.5 重置 IDENTITY 种子（仅 DELETE 过的表；TRUNCATE 已自动重置） ---------- */
SET @stmt = N'';
SELECT @stmt = @stmt
+ N'DBCC CHECKIDENT(''' + REPLACE(t.quoted, '''', '''''') + N''', RESEED, 0) WITH NO_INFOMSGS;'
+ CHAR(10)
FROM #tabs t
WHERE t.status LIKE N'OK(DELETE%'
AND EXISTS (SELECT 1 FROM sys.identity_columns ic
WHERE ic.object_id = OBJECT_ID(t.quoted));
IF LEN(@stmt) > 0 EXEC sp_executesql @stmt;
PRINT '>>> IDENTITY 种子已重置。';
/* ---------- 2.6 恢复约束：WITH CHECK 重新验证（表已空，验证无成本） ---------- */
SET @stmt = N'';
SELECT @stmt = @stmt + N'ALTER TABLE ' + quoted + N' WITH CHECK CHECK CONSTRAINT ALL;' + CHAR(10)
FROM #tabs;
EXEC sp_executesql @stmt;
PRINT '>>> 外键与 CHECK 约束已恢复并置为已信任状态。';
/* ---------- 2.7 恢复触发器（仅恢复原本处于启用状态的） ---------- */
SET @stmt = N'';
SELECT @stmt = @stmt + N'ENABLE TRIGGER ' + QUOTENAME(trg_name)
+ N' ON ' + quoted + N';' + CHAR(10)
FROM #trg
WHERE was_disabled = 0;
IF LEN(@stmt) > 0 EXEC sp_executesql @stmt;
PRINT '>>> DML 触发器已恢复。';
/* ---------- 2.8 汇总报告 ---------- */
DECLARE @okT INT, @okD INT, @fail INT;
SELECT @okT = SUM(CASE WHEN status LIKE N'OK(TRUNCATE%' THEN 1 ELSE 0 END),
@okD = SUM(CASE WHEN status LIKE N'OK(DELETE%' THEN 1 ELSE 0 END),
@fail = SUM(CASE WHEN status LIKE N'FAIL%' THEN 1 ELSE 0 END)
FROM #tabs;
PRINT '';
PRINT '============================================================';
PRINT ' 清空结果汇总';
PRINT '============================================================';
PRINT ' 处理表总数 ：' + CONVERT(varchar(10), @total);
PRINT ' 成功(TRUNCATE) ：' + CONVERT(varchar(10), ISNULL(@okT, 0));
PRINT ' 成功(DELETE) ：' + CONVERT(varchar(10), ISNULL(@okD, 0));
PRINT ' 失败 ：' + CONVERT(varchar(10), ISNULL(@fail, 0));
PRINT '============================================================';
-- 失败清单（正常应为 0 行）
SELECT id AS 序号,
quoted AS 表,
action AS 采用方式,
status AS 结果
FROM #tabs
WHERE status LIKE N'FAIL%'
ORDER BY id;
-- 完整明细（按行数倒序，便于核对）
SELECT quoted AS 表,
row_count AS 清空前行数,
action AS 采用方式,
status AS 结果
FROM #tabs
ORDER BY row_count DESC, quoted;
GO
/* =====================================================================
第 3 步（只读）：收尾自检
—— 正常应返回 0 行（即已无任何表的剩余行数 > 0）
===================================================================== */
-- 3.1 残留数据的表
SELECT s.name AS 架构,
t.name AS 表名,
SUM(ISNULL(p.rows, 0)) AS 剩余行数
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
WHERE t.is_ms_shipped = 0
AND t.name NOT IN (N'dtproperties', N'sysdiagrams')
GROUP BY s.name, t.name
HAVING SUM(ISNULL(p.rows, 0)) > 0
ORDER BY 剩余行数 DESC;
-- 3.2 禁用状态残留检查（正常应返回 0 行）
SELECT '外键' AS 类型, OBJECT_NAME(fk.parent_object_id) AS 表名, fk.name AS 名称
FROM sys.foreign_keys fk
WHERE fk.is_disabled = 1
UNION ALL
SELECT 'CHECK 约束', OBJECT_NAME(cc.parent_object_id), cc.name
FROM sys.check_constraints cc
WHERE cc.is_disabled = 1
UNION ALL
SELECT '触发器', OBJECT_NAME(tr.parent_id), tr.name
FROM sys.triggers tr
WHERE tr.is_disabled = 1
AND tr.parent_class = 1;
-- 3.3 表结构与索引完好性确认
SELECT COUNT(*) AS 用户表数量
FROM sys.tables
WHERE is_ms_shipped = 0 AND name NOT IN (N'dtproperties', N'sysdiagrams');
SELECT COUNT(*) AS 索引总数
FROM sys.indexes i
JOIN sys.tables t ON t.object_id = i.object_id
WHERE t.is_ms_shipped = 0 AND i.index_id > 0;
SELECT COUNT(*) AS 列总数
FROM sys.columns c
JOIN sys.tables t ON t.object_id = c.object_id
WHERE t.is_ms_shipped = 0;
GO
PRINT '>>> 全部步骤执行完毕。若 3.1 返回 0 行、3.2 返回 0 行，说明数据已清空且约束/触发器状态正常。';
GO
/* =====================================================================
附：常见后续操作（按需手工执行，本脚本未包含）
---------------------------------------------------------------------
-- 收缩数据库文件（清空大量数据后释放磁盘；会锁表，务必在维护窗口做）
-- DBCC SHRINKDATABASE (N'DB_SH01', 10);
-- 单个文件收缩
-- DBCC SHRINKFILE (N'DB_SH01_Data', 1024);
===================================================================== */
/* =====================================================================
附：关于性能 —— 什么时候 @UseTruncate 也不够快
---------------------------------------------------------------------
DELETE 的代价与行数、以及表上有多少非聚集索引成正比，而且会完整写事务日志。
经验值：百万行级以上的表，DELETE 可能要几分钟到几十分钟，日志文件会明显膨胀。
如果你的库存在这种量级的表，最快的路径不是 TRUNCATE，而是：
DROP 所有外键 -> TRUNCATE 全部表 -> 按原定义重建外键
TRUNCATE 是页级释放，几乎瞬时且日志量极小。代价是外键必须先物理删除，
重建外键的语句需要从 sys.foreign_keys / sys.foreign_key_columns 生成。
判断是否需要走这条路，先跑这一句看最大的表：
SELECT TOP 20 OBJECT_NAME(object_id) AS 表, SUM(rows) AS 行数
FROM sys.partitions WHERE index_id IN (0,1)
GROUP BY object_id ORDER BY SUM(rows) DESC;
===================================================================== */
最后修改：2026 年 10 月 28 日
© 允许规范转载