SQLSERVER清空(Truncate)被外键引用的数据表

前言：我们知道SQLSERVER清空数据表有两种方式Delete和Truncate，当然两者的不同大家也都知道(不清楚的可以MSDN)。不过这个错误“Cannot truncate table  because it is being referenced by a FOREIGN KEY” 相信大家也都遇到过，解决的已解决，未解决的且看下文。

如何解决

开始我以为只要将外键Disable掉就可以了，事实证明是没用的。其实MSDN已经明确告诉了我们：

不能对以下表使用 TRUNCATE TABLE：

• 由 FOREIGN KEY 约束引用的表。（您可以截断具有引用自身的外键的表。）
• 参与索引视图的表。
• 通过使用事务复制或合并复制发布的表。

对于具有以上一个或多个特征的表，请使用 DELETE 语句。

TRUNCATE TABLE 不能激活触发器，因为该操作不记录各个行删除

难道我真的要用Delete吗？可我真的不想用Delete。原因就在于Truncate的优点，MSDN说：

与 DELETE 语句相比，TRUNCATE TABLE 具有以下优点：

• 所用的事务日志空间较少。
  DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一个项。TRUNCATE TABLE 通过释放用于存储表数据的数据页来删除数据，并且在事务日志中只记录页释放。
• 使用的锁通常较少。
  当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。TRUNCATE TABLE 始终锁定表和页，而不是锁定各行。
• 如无例外，在表中不会留有任何页。
  执行 DELETE 语句后，表仍会包含空页。(略去例如)

好了，下面就来说一下解决方法。

解决方案

1.使用Delete

a) 先Delete依赖表（或叫从表）

b) 再Delete被依赖表（或叫主表）

2.使用Truncate

a) 先备份依赖表外键

b) 删除依赖表外键

c) Truncate主表

d) 重新创建依赖表外键

一段脚本

其实是一个使用Truncate进行处理的存储过程，思路见上。

USE <YOUR DB>
GO

CREATE PROCEDURE [dbo].[usp_Truncate_Table]
  @TableToTruncate VARCHAR(64)
AS 

BEGIN

SET NOCOUNT ON

--==变量定义
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)   
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

 SET @Debug = 0--(0:将执行相关语句|1:不执行语句)
 SET @Recycle = 0--(0:不创建/不清除存储表|1:将创建/清理存储表)
 set @Verbose = 1--(1:每步执行均打印消息|0:不打印消息)

 SET @i = 1
    SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>]  WITH NOCHECK ADD  CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
    SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
    SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- 创建外键临时表
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
    DROP TABLE #FKs

-- 获取外键
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       OBJECT_NAME(parent_object_id) as TableName,
       clm1.name as ColumnName, 
       OBJECT_NAME(referenced_object_id) as ReferencedTableName,
       clm2.name as ReferencedColumnName
  INTO #FKs
  FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
 WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
 ORDER BY OBJECT_NAME(parent_object_id)

-- 外键操作(删除|重建)表
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
    IF @Verbose = 1
        PRINT '1. 正在创建表(Internal_FK_Definition_Storage)...'
    CREATE TABLE [Internal_FK_Definition_Storage] 
    (
        ID int not null identity(1,1) primary key,
        FK_Name varchar(250) not null,
        FK_CreationStatement varchar(max) not null,
        FK_DestructionStatement varchar(max) not null,
        Table_TruncationStatement varchar(max) not null
    ) 
END 
ELSE
BEGIN
    IF @Recycle = 0
    BEGIN
        IF @Verbose = 1
        PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
        TRUNCATE TABLE [Internal_FK_Definition_Storage]    
    END
    ELSE
        PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
END

IF @Recycle = 0
BEGIN
    IF @Verbose = 1
        PRINT '2. 正在备份外键定义...'           
    WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
    BEGIN
        SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
        SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
        SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
        SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
        SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

        SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
        SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
        SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName) 

        INSERT INTO [Internal_FK_Definition_Storage]
        SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
        
        SET @i = @i + 1
        
        IF @Verbose = 1
            PRINT '  > 已备份外键:[' + @ConstraintName + '] 所属表: [' + @TableName + ']'
    END   
END   
ELSE 
    PRINT '2. 正在备份外键定义...'

IF @Verbose = 1
    PRINT '3. 正在删除外键...'
BEGIN TRAN    
BEGIN TRY
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
        PRINT '  > 已删除外键:[' + @ConstraintName + ']'
END     

IF @Verbose = 1
    PRINT '4. 正在清理数据表...'
--先清除该外键所在表(由于外键所在表仍可能又被其他外键所引用，因此需要循环递归处理)(注：本处理未实现)
--请不要使用下面注释代码
/*    
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
        PRINT '  > ' + @Statement
END
*/

IF @Debug = 1 
    PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
    EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
IF @Verbose = 1
    PRINT '  > 已清理数据表[' + @TableToTruncate + ']'
    
IF @Verbose = 1
    PRINT '5. 正在重建外键...'
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
    PRINT '  > 已重建外键:[' + @ConstraintName + ']'
END
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK 
    PRINT '出错信息:'+ERROR_MESSAGE()
END CATCH
IF @Verbose = 1
    PRINT '6. 处理完成！'
END

如何使用

例子说明：清空整个数据库

USE <YOUR DB>
GO

--==创建临时表
IF(OBJECT_ID('TEMPDB..#TEMP')IS NOT NULL)
    DROP TABLE #TEMP

--==读取数据库表
SELECT SN=ROW_NUMBER()OVER(ORDER BY [name]ASC),TableName=[name]
INTO #TEMP
FROM sys.tables 
WHERE [name]<>'Internal_FK_Definition_Storage'

--SELECT * FROM #TEMP

--==开始处理
DECLARE @ROWS INT 
SELECT @ROWS=MAX(SN)FROM #TEMP
DECLARE @I INT
SET @I=1
DECLARE @TableName VARCHAR(64)
WHILE(@I<=@ROWS)
BEGIN
    IF(EXISTS(SELECT 1 FROM #TEMP WHERE SN=@I))
    BEGIN
        SELECT @TableName=TableName FROM #TEMP WHERE SN=@I
        EXEC [dbo].[usp_Truncate_Table] @TableToTruncate = @TableName
    END
    SET @TableName=N''
    SET @I=@I+1
END

 

结束语：文章无甚深浅，止乎于分享。如有错误，还望斧正。