SQL Server - Truncate Table

The other day I ran into an issue where a SQL table was used as a staging area. Application would insert data into this table and then a user will review the contents of this table via the application, make any corrections and then click on "Verified". When Verified was clicked, the application will execute a stored procedure which will copy data from this table to a permanent table and then delete the records from the staging table. Depending on the number of records, the delete could take a while. Further, since the table had an identity key which was defined as SMALL INT, the table was frequently running out of ID values and had to be reseeded to start all over.

The fix was rather simple. Since this was a stand alone table and the data was deleted after it was successfully copied over to another table, the stored procedure could have just truncated the table instead of deleting the records. Truncation would have been faster and it would reset the table every time, resulting in identity key starting from initial value (1 in this case).

What if you had a table that you want to truncate every now and then but it also has foreign key constraints with other tables. Obviously, first you will have to remove the constraints, truncate the table and add the constraints back. This could get cumbersome rather quickly. Fortunately, you could create a generic stored procedure which will accept the table name, figure out any constraints on this table, drop them, truncate the table and recreate the constraints. It can then be used to truncate any other table (although you better be careful, because you may end up truncating live data tables).

Here is a stored procedure that I have used in the past.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[sp_TruncateTable]
    @TableName    varchar(255)

AS

SET NOCOUNT ON

DECLARE @strSQL    nvarchar(MAX)

DECLARE @CheckForeignKeys TABLE
(
    Name            varchar(100),
    ParentColumn    varchar(100),
    ChildTable        varchar(100),
    ChildColumn        varchar(100))

--    Get Foreign Key info
INSERT INTO @CheckForeignKeys
SELECT
    FK.Name,
    PC.Name,
    CT.Name,
    CC.Name

FROM SYS.TABLES T
    INNER JOIN SYS.FOREIGN_KEYS FK
        ON FK.REFERENCED_OBJECT_ID = T.OBJECT_ID
    INNER JOIN SYS.TABLES CT
        ON CT.OBJECT_ID = FK.PARENT_OBJECT_ID
    INNER JOIN SYS.FOREIGN_KEY_COLUMNS FKC
        ON FKC.CONSTRAINT_OBJECT_ID = FK.OBJECT_ID
    INNER JOIN SYS.COLUMNS PC
        ON PC.OBJECT_ID = FKC.REFERENCED_OBJECT_ID
        AND PC.COLUMN_ID = FKC.REFERENCED_COLUMN_ID
    INNER JOIN SYS.COLUMNS CC
        ON CC.OBJECT_ID = FKC.PARENT_OBJECT_ID
        AND CC.COLUMN_ID = FKC.PARENT_COLUMN_ID

WHERE T.Name = @TableName

SET NOCOUNT OFF

--    Drop foreign keys
PRINT 'Dropping Foreign Keys'

DECLARE DropConstraints CURSOR FAST_FORWARD FOR
    SELECT 'ALTER TABLE ' + FK.ChildTable + ' DROP CONSTRAINT ' + FK.Name
    FROM @CheckForeignKeys FK
    
OPEN DropConstraints
FETCH NEXT FROM DropConstraints INTO @strSQL
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executeSQL @strSQL
    PRINT @strSQL
    FETCH NEXT FROM DropConstraints INTO @strSQL
END

CLOSE DropConstraints
DEALLOCATE DropConstraints

--    Truncate table
BEGIN TRY

    SELECT @strSQL = 'TRUNCATE TABLE ' + @TableName
    PRINT @strSQL
    EXEC sp_executeSQL @strSQL

END TRY
BEGIN CATCH
    PRINT 'ERROR OCCURRED -- ' + @@Error
END CATCH


--Add constraints
PRINT 'Adding Constraints'
DECLARE AddConstraints CURSOR FAST_FORWARD FOR

SELECT 'ALTER TABLE ' + FK.ChildTable + ' WITH NOCHECK ADD CONSTRAINT ' + FK.Name + 
       ' FOREIGN KEY (' + FK.ChildColumn + ') REFERENCES ' + @TableName + '(' + FK.ParentColumn + ')
ALTER TABLE ' + FK.ChildTable + ' CHECK CONSTRAINT ' + FK.Name

    FROM @CheckForeignKeys FK

OPEN AddConstraints

FETCH NEXT FROM AddConstraints INTO @strSQL
WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_executeSQL @strSQL
    PRINT @strSQL
    FETCH NEXT FROM AddConstraints INTO @strSQL

END

CLOSE AddConstraints
DEALLOCATE AddConstraints
PRINT 'DONE!'
GO

If I execute this stored procedure, passing the table name I want to truncate, here is the message I get...

EXEC sp_TruncateTable 'Customers_Audit'


And here is the output...

Dropping Foreign Keys
ALTER TABLE Customers_Audit2 DROP CONSTRAINT FK_Customers_Audit2_Customers_Audit
TRUNCATE TABLE Customers_Audit
Adding Constraints
ALTER TABLE Customers_Audit2 WITH NOCHECK ADD CONSTRAINT FK_Customers_Audit2_Customers_Audit FOREIGN KEY (CustomerID) REFERENCES Customers_Audit(CustomerID)
ALTER TABLE Customers_Audit2 CHECK CONSTRAINT FK_Customers_Audit2_Customers_Audit
DONE!

Keep in mind that sp_executeSQL expects unicode parameter that's why I had to declare @strSQL as nvarchar(MAX).

Thank You.